
with dwd_tab_send_arrival_base_dt as(
select
     scan.waybill_no            as waybill_no          --运单号
    ,scan.scan_time             as scan_time           --扫描时间
    ,scan.input_time            as input_time          --录入时间
    ,dim.code                   as scan_site_code      --扫描网点编码
    ,dim.name                   as scan_site_name      --扫描网点名称
    ,scan.next_station_code     as next_station_code   --上下一站
    ,next_dim.network_type      as next_network_type   --下下一站类型
    ,scan.transfer_code         as transfer_code       --任务号
    ,scan.scan_type             as scan_type           --扫描类型
    ,dim.network_type           as network_type        --当前站类型
from (
    select
         send.waybill_no                                    as waybill_no         --运单号
        ,nvl(send.scan_time        ,load.scan_time        ) as scan_time          --扫描时间
        ,nvl(send.input_time       ,load.input_time       ) as input_time         --录入时间
        ,nvl(send.scan_site_code   ,load.scan_site_code   ) as scan_site_code     --扫描网点编码
        ,nvl(send.scan_site_name   ,load.scan_site_name   ) as scan_site_name     --扫描网点名称
        ,nvl(send.next_station_code,load.next_station_code) as next_station_code  --下一站code
        ,nvl(send.transfer_code    ,load.transfer_code    ) as transfer_code      --转单号
        ,send.scan_type                                     as scan_type          --扫描类型
    from(
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,input_site           as scan_site_name     --扫描网点名称
            ,next_station_code    as next_station_code  --下一站code
            ,shipment_no          as transfer_code      --任务号
            ,'send'               as scan_type          --扫描类型
       from jms_dwd.dwd_tab_barscan_send_base_hi  --发件
        where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',1) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
          and scan_time between concat(date_sub('{{ execution_date | cst_ds }}',1),' 00:00:00') and concat(date_add('{{ execution_date | cst_ds }}',1),' 07:00:00')
          and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) send
    left join (
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,scan_site            as scan_site_name     --扫描网点名称
            ,next_station_code    as next_station_code  --下一站code
            ,shipment_no          as transfer_code      --任务号
            ,'load'               as scan_type
        from jms_dwd.dwd_tab_barscan_loading_base_hi --装车
        where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',1) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
         and  scan_time between concat(date_sub('{{ execution_date | cst_ds }}',1),' 00:00:00') and concat(date_add('{{ execution_date | cst_ds }}',1),' 07:00:00')
         and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) load on send.waybill_no = load.waybill_no
          and send.scan_site_code = load.scan_site_code
          and send.scan_time = load.scan_time
    union all
    select
         arrival.waybill_no                                      as waybill_no         --运单号
        ,nvl(arrival.scan_time        ,unload.scan_time        ) as scan_time          --扫描时间
        ,nvl(arrival.input_time       ,unload.input_time       ) as input_time         --录入时间
        ,nvl(arrival.scan_site_code   ,unload.scan_site_code   ) as scan_site_code     --扫描网点编码
        ,nvl(arrival.scan_site_name   ,unload.scan_site_name   ) as scan_site_name     --扫描网点名称
        ,nvl(arrival.next_station_code,unload.next_station_code) as next_station_code  --转单号
        ,nvl(arrival.transfer_code    ,unload.transfer_code    ) as transfer_code      --转单号
        ,arrival.scan_type                                       as scan_type          --扫描类型
    from(
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,scan_site            as scan_site_name     --扫描网点名称
            ,pre_station_code     as next_station_code  --上一站
            ,transfer_code        as transfer_code      --任务号
            ,'arrival'            as scan_type          --扫描类型
        from jms_dwd.dwd_tab_barscan_arrival_base_hi arrival --到件
        where substr(dt,1,10)  between date_sub('{{ execution_date | cst_ds }}',1) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
          and scan_time between concat(date_sub('{{ execution_date | cst_ds }}',1),' 00:00:00') and concat(date_add('{{ execution_date | cst_ds }}',1),' 07:00:00')
          and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) arrival
    left join (
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,scan_site            as scan_site_name     --扫描网点名称
            ,pre_station_code     as next_station_code  --上一站
            ,shipment_no          as transfer_code      --任务号
            ,'unload'             as scan_type          --扫描类型
        from jms_dwd.dwd_tab_barscan_unloading_base_hi --卸车
        where substr(dt,1,10)  between date_sub('{{ execution_date | cst_ds }}',1) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
         and scan_time between concat(date_sub('{{ execution_date | cst_ds }}',1),' 00:00:00') and concat(date_add('{{ execution_date | cst_ds }}',1),' 07:00:00')
         and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) unload on arrival.waybill_no = unload.waybill_no
            and arrival.scan_site_code = unload.scan_site_code
            and arrival.scan_time = unload.scan_time
) scan
left join (
    select
         code
        ,name
        ,network_type
    from jms_dim.dim_network_whole_massage --网点维度表 当前站
) dim on scan.scan_site_code = dim.code
left join (
    select
         code
        ,name
        ,network_type
    from jms_dim.dim_network_whole_massage --网点维度表 下一站
) next_dim on scan.next_station_code = next_dim.code
),
-- 到件数据保留第一条数据， 发件数据保留最后一条数据
send_arrival_first_base_dt as (
    select
        waybill_no
        , scan_time
        , input_time
        , scan_site_code
        , scan_site_name
        , next_station_code
        , next_network_type
        , transfer_code
        , scan_type
        , network_type
    from (
        select
            *
            , row_number() over(partition by waybill_no,scan_site_code,scan_type order by scan_time asc)  as rk_asc
            , row_number() over(partition by waybill_no,scan_site_code,scan_type order by scan_time desc) as rk_desc
        from
            dwd_tab_send_arrival_base_dt
    )
    where
        case when scan_type = 'arrival' then rk_asc  = 1
             when scan_type = 'send'    then rk_desc = 1
        end
),
-- 只保留第一个中心和之前的数据, 并给每个站点按照站点类型排序
dwd_tab_wide_base_dt as (
    select
        waybill_no
        , scan_time
        , input_time
        , scan_site_code
        , scan_site_name
        , next_station_code
        , next_network_type
        , transfer_code
        , scan_type
        , network_type
        , lag_site_code
        , sum(case when scan_site_code <> nvl(lag_site_code,1) then 1 else 0 end)
          over (partition by waybill_no, network_type order by scan_time )      as network_type_rk
    from (
        select
            *
            , sum(case when scan_site_code <> lag_site_code and network_type = 4 then 1 else 0 end)
              over (partition by waybill_no order by scan_time )                as outport_flag
        from (
            select
                waybill_no
                , scan_time
                , input_time
                , scan_site_code
                , scan_site_name
                , next_station_code
                , next_network_type
                , transfer_code
                , scan_type
                , network_type
                , lag(scan_site_code, 1, null) over(partition by waybill_no order by scan_time) as lag_site_code
            from
                send_arrival_first_base_dt
        ) a
    ) b
    where
        outport_flag <= 1
),
---补下一站逻辑
dwd_tab_send_next_base_dt as (
   select *
   from (
       select
             *,row_number() over(partition by waybill_no,next_station_code order by scan_time desc) as rk --按照最晚取，
       from dwd_tab_wide_base_dt --装车发件
       where scan_type = 'send'
       and next_station_code is not null
   ) a where a.rk = 1
)



---------------------------第一段：拿出T-2天到T天7点到发件数据取出始发网点、始发集散、始发中心
insert overwrite table jms_tmp.dwd_tab_barscan_base_dt
select
    wide.waybill_no        --运单号
   ,wide.scan_time         --扫描时间
   ,wide.input_time        --录入时间
   ,wide.scan_site_code    --扫描网点编码
   ,wide.scan_site_name    --扫描网点名称
   ,nvl(wide.next_station_code,send_next.scan_site_code) --转单号
   ,wide.transfer_code     --转单号
   ,wide.network_type
   ,wide.next_network_type
   ,wide.scan_type         --扫描类型
   ,send_center.first_center_send_time
   ,wide.network_type_rk
from dwd_tab_wide_base_dt wide --发件
left join dwd_tab_send_next_base_dt send_next
on wide.waybill_no = send_next.waybill_no --关联发件取发件当前站 = 到件上一站
and wide.scan_site_code = send_next.next_station_code
and wide.scan_time > send_next.scan_time
and wide.scan_type = 'arrival'
left join (
    select
         waybill_no
        ,min(scan_time) as first_center_send_time
    from dwd_tab_send_arrival_base_dt
    where scan_type= 'send'
    and network_type = 4
    group by waybill_no
) send_center on wide.waybill_no = send_center.waybill_no
where
    -- 20230818: 保留双集散, 其他环节只要第一个站点
    case when wide.network_type = 5 then wide.network_type_rk <= 2
         else wide.network_type_rk = 1
    end
;







insert overwrite table jms_tmp.dwd_tab_barscan_arrival_send_base_dt
select
     base.waybill_no          as waybill_no              --运单号
    ,base.actual_send_time    as actual_send_time        --网点实际发件时间
    ,base.first_network_code  as first_network_code      --始发网点编码
    ,base.first_nodal_name    as first_nodal_name        --始发集散名称
    ,base.first_nodal_code    as first_nodal_code        --始发集散编码
    ,base.first_center_code   as first_center_code       --始发中心编码
    ,base.first_center_name   as first_center_name       --始发中心名称
    ,base.first_center_code   as actual_center_code      --实际交件中心编码
    ,base.first_center_name   as actual_center_name      --实际交件中心名称
    ,base.first_center_time   as actual_center_time      --实际交件中心扫描时间
    ,case when branch.arrival_center_arrival_time is not null
          then least(nvl(branch.arrival_center_arrival_time,'2099-12-31 00:00:00') --到车任务号
                    ,nvl(base.first_center_time            ,'2099-12-31 00:00:00'))
          when branch.send_center_arrival_time is not null
          then least(nvl(branch.send_center_arrival_time   ,'2099-12-31 00:00:00')--发车任务号
                    ,nvl(base.first_center_time            ,'2099-12-31 00:00:00'))
          else base.first_center_time end as actual_center_arr_time
    ,base.first_nodal_code   as actual_collect_code         --实际交件集散点编码
    ,base.first_nodal_name   as actual_collect_name         --实际交件集散点名称
    ,base.first_nodal_time   as plan_collect_time           --规划到达集散时间
    ,base.first_nodal_code   as plan_collect_code           --规划到达集散编码
    ,base.first_nodal_name   as plan_collect_name           --规划到达集散名称
    ,base.first_nodal_time   as actual_collect_time         --实际交件集散点时间
    ,coalesce(branch.arrival_nodal_arrival_time
             ,branch.send_nodal_arrival_time
             ,base.first_nodal_time
    ) as actual_collect_arr_time    --实际交件集散点时间（包含支线任务到达时间）兜底逻辑到件、发件下一站、卸车、装车下一站
    ,base.first_nodal_time  as first_nodal_time           --始发集散点时间
    ,base.first_center_time as first_center_time          --始发中心时间
    ,coalesce(branch.arrival_center_shipment_no
             ,branch.send_center_shipment_no
     ) as actual_center_shipment_no
    ,base.first_nodal_send_time    as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
    ,base.first_nodal_arrival_time as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
    ,base.first_center_send_time   as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
    ,nvl(base.first_center_arrival_time,base.first_center_send_time) as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
    ,coalesce(branch.arrival_center_shipment_shifts
             ,branch.send_center_shipment_shifts
     ) as actual_center_shipment_shifts --实际交件支线任务班次
    ,branch.send_network_shipment_no as actual_network_shipment_no
    ,coalesce(branch.arrival_nodal_shipment_no
             ,branch.send_nodal_shipment_no
     )  as actual_nodal_shipment_no
     -- 如果重复扫描导致第二集散和第一集散值一样, 第二集散置空
     ,case when first_nodal_code = nvl(second_nodal_code,'0') then null else base.second_nodal_code         end as second_nodal_code        -- 第二集散code
     ,case when first_nodal_code = nvl(second_nodal_code,'0') then null else base.second_nodal_name         end as second_nodal_name        -- 第二集散名称
     ,case when first_nodal_code = nvl(second_nodal_code,'0') then null else base.second_nodal_time         end as second_nodal_time        -- 到达第二集散时间（到件发件扫描中最小时间）
     ,case when first_nodal_code = nvl(second_nodal_code,'0') then null else base.second_nodal_send_time    end as second_nodal_send_time   -- 第二集散到件扫描时间
     ,case when first_nodal_code = nvl(second_nodal_code,'0') then null else base.second_nodal_arrival_time end as second_nodal_arrival_time-- 第二集散发件扫描时间
     ,case when first_nodal_code = nvl(second_nodal_code,'0') then null
           else coalesce(branch.arrival_second_nodal_arrival_time
                    ,branch.send_second_nodal_arrival_time
                    ,base.second_nodal_time
                )
     end as actual_second_collect_arr_time     -- 第二集散实际到达时间
     ,case when first_nodal_code = nvl(second_nodal_code,'0')  then null
           else coalesce(branch.arrival_second_nodal_shipment_no
                        ,branch.send_second_nodal_shipment_no
                )
    end as actual_second_nodal_shipment_no
    ,branch.send_center_arrival_time     --  发件任务单到中心时间
    ,branch.arrival_center_arrival_time  --  到件任务单到中心时间
from (
-- 补充到件发件扫描的到达时间作为扫描时间
    select
        waybill_no
        , max(actual_send_time)            as actual_send_time
        , max(first_network_code)          as first_network_code
        , min(first_nodal_time)            as first_nodal_time
        , max(first_nodal_code)            as first_nodal_code
        , max(first_nodal_name)            as first_nodal_name
        , min(first_center_time)           as first_center_time
        , max(first_center_code)           as first_center_code
        , max(first_center_name)           as first_center_name
        , min(second_nodal_time)           as second_nodal_time
        , max(second_nodal_code)           as second_nodal_code
        , max(second_nodal_name)           as second_nodal_name
        , max(first_nodal_send_time)       as first_nodal_send_time
        , min(first_nodal_arrival_time)    as first_nodal_arrival_time
        , max(first_center_send_time)      as first_center_send_time
        , max(first_center_arrival_time)   as first_center_arrival_time
        , max(second_nodal_send_time)      as second_nodal_send_time
        , min(second_nodal_arrival_time)   as second_nodal_arrival_time
        from(
            select
                waybill_no
                ,case when network_type = 6  and  scan_type = 'send'  then scan_time      end                           as actual_send_time            --网点实际发件时间/最早发件时间
                ,case when network_type = 6  and  scan_type = 'send'  then scan_site_code end                           as first_network_code          --始发网点编码
                ,case when network_type = 5  and  network_type_rk = 1 then scan_time      end                           as first_nodal_time            --第一集散实际交件集散点时间
                ,case when network_type = 5  and  network_type_rk = 1 then scan_site_code end                           as first_nodal_code            --第一集散实际交件集散点编码
                ,case when network_type = 5  and  network_type_rk = 1 then scan_site_name end                           as first_nodal_name            --第一集散实际交件集散点名称
                ,case when network_type = 4  then scan_time      end                                                    as first_center_time           --实际交件中心扫描时间
                ,case when network_type = 4  then scan_site_code end                                                    as first_center_code           --实际交件中心编码
                ,case when network_type = 4  then scan_site_name end                                                    as first_center_name           --实际交件中心名称
                ,case when network_type = 5  and scan_type = 'send'    and  network_type_rk = 1  then scan_time end     as first_nodal_send_time       --第一集散始发集散发件扫描时间 (20221113新增)
                ,case when network_type = 5  and scan_type = 'arrival' and  network_type_rk = 1  then scan_time end     as first_nodal_arrival_time    --第一集散始发集散到件扫描时间 (20221113新增)
                ,case when network_type = 4  and scan_type = 'arrival'                           then scan_time end     as first_center_arrival_time   --始发转运中心到件扫描时间 (20221113新增)
                ,case when network_type = 5  and  network_type_rk = 2 then scan_time      end                           as second_nodal_time           --第二集散实际交件集散点时间
                ,case when network_type = 5  and  network_type_rk = 2 then scan_site_code end                           as second_nodal_code           --第二集散实际交件集散点编码
                ,case when network_type = 5  and  network_type_rk = 2 then scan_site_name end                           as second_nodal_name           --第二集散实际交件集散点名称
                ,first_center_send_time                                                                                                                --始发转运中心发件扫描时间
                ,case when network_type = 5  and scan_type = 'send'    and  network_type_rk = 2  then scan_time end     as second_nodal_send_time      --第二集散始发集散发件扫描时间(20230818新增)
                ,case when network_type = 5  and scan_type = 'arrival' and  network_type_rk = 2  then scan_time end     as second_nodal_arrival_time   --第二集散始发集散到件扫描时间(20230818新增)
            from
                jms_tmp.dwd_tab_barscan_base_dt
        ) base
        group by waybill_no
) base
left join (
-- 补充到件发件扫描对应的任务单信息时间作为实际到达时间
    select
         waybill_no as waybill_no              --运单号
        ,max(arrival_nodal_arrival_time    )    as arrival_nodal_arrival_time     --优先取到件扫描对应支线实际到达时间
        ,max(send_nodal_arrival_time       )    as send_nodal_arrival_time        --次取发件扫描对应支线实际到达时间
        ,max(arrival_center_arrival_time   )    as arrival_center_arrival_time    --优先取到件扫描对应支线实际到达时间
        ,max(send_center_arrival_time      )    as send_center_arrival_time       --次取发件扫描对应支线实际到达时间
        ,max(arrival_center_shipment_no    )    as arrival_center_shipment_no     --优先取到件扫描对应支线任务号
        ,max(send_center_shipment_no       )    as send_center_shipment_no        --次取发件扫描对应支线任务号
        ,max(arrival_center_shipment_shifts)    as arrival_center_shipment_shifts --优先取到件扫描对应支线任务号
        ,max(send_center_shipment_shifts   )    as send_center_shipment_shifts    --次取发件扫描对应支线任务号
        ,max(send_network_shipment_no)          as send_network_shipment_no       --网点直接取发件表任务单
        ,max(arrival_nodal_shipment_no)         as arrival_nodal_shipment_no      --优先取到件扫描对应支线任务号(集散)
        ,max(send_nodal_shipment_no)            as send_nodal_shipment_no         --次取发件扫描对应支线任务号(集散)
        ,max(arrival_second_nodal_arrival_time) as arrival_second_nodal_arrival_time   -- 优先取到件扫描对应支线实际到达时间(第二集散)
        ,max(send_second_nodal_arrival_time)    as send_second_nodal_arrival_time      -- 次取发件扫描对应支线实际到达时间(第二集散)
        ,max(arrival_second_nodal_shipment_no)  as arrival_second_nodal_shipment_no    -- 优先取到件扫描对应支线任务号(第二集散)
        ,max(send_second_nodal_shipment_no)     as send_second_nodal_shipment_no       -- 次取发件扫描对应支线任务号(第二集散)
    from (
         select
               base.waybill_no     --运单号
              ,base.scan_time      --扫描时间
              ,base.input_time     --录入时间
              ,base.scan_site_code --扫描网点编码
              ,base.scan_site_name --扫描网点名称
              ,base.scan_type      --扫描类型
              ,case when base.network_type      = 5 and base.scan_type = 'arrival' and network_type_rk = 1 then stop.actual_arrival_time end as arrival_nodal_arrival_time  --优先取到件扫描对应支线实际到达时间
              ,case when base.network_type      = 6 and base.next_network_type = 5 and base.scan_type = 'send' and network_type_rk = 1 then stop.actual_arrival_time end as send_nodal_arrival_time --次取发件扫描对应支线实际到达时间
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.actual_arrival_time end as arrival_center_arrival_time --优先取到件扫描对应支线实际到达时间
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.actual_arrival_time end as send_center_arrival_time    --次取发件扫描对应支线实际到达时间
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.shipment_no end as arrival_center_shipment_no          --优先取到件扫描对应支线任务号
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.shipment_no end as send_center_shipment_no             --次取发件扫描对应支线任务号
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.shipment_shifts end as arrival_center_shipment_shifts  --优先取到件扫描对应支线任务班次
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.shipment_shifts end as send_center_shipment_shifts     --次取发件扫描对应支线任务班次
              ,case when base.network_type      = 6 and base.scan_type = 'send'    then base.transfer_code end as send_network_shipment_no          --网点直接取发件表任务单
              ,case when base.network_type      = 5 and base.scan_type = 'arrival'  and network_type_rk = 1 then stop.shipment_no end as arrival_nodal_shipment_no           --优先取到件扫描对应支线任务号
              ,case when base.network_type      = 6 and base.next_network_type = 5  and base.scan_type = 'send' and network_type_rk = 1 then stop.shipment_no end as send_nodal_shipment_no --次取发件扫描对应支线任务号
              ,case when base.network_type      = 5 and base.scan_type = 'arrival'  and network_type_rk = 2 then stop.actual_arrival_time end as arrival_second_nodal_arrival_time   --优先取到件扫描对应支线实际到达时间
              ,case when base.next_network_type = 5 and base.scan_type = 'send'     and network_type_rk = 2 then stop.actual_arrival_time end as send_second_nodal_arrival_time      --次取发件扫描对应支线实际到达时间
              ,case when base.network_type      = 5 and base.scan_type = 'arrival'  and network_type_rk = 2 then stop.shipment_no end as arrival_second_nodal_shipment_no           --优先取到件扫描对应支线任务号
              ,case when base.next_network_type = 5 and base.scan_type = 'send'     and network_type_rk = 2 then stop.shipment_no end as send_second_nodal_shipment_no              --次取发件扫描对应支线任务号
         from (
            select
                 waybill_no
                ,scan_time
                ,input_time
                ,scan_site_code
                ,scan_site_name
                ,next_station_code
                ,transfer_code
                ,network_type
                ,next_network_type
                ,scan_type
                ,network_type_rk
                ,max(center_code)     over(partition by waybill_no)               as next_pre_site_code
                -- ,max(network_type_rk) over(partition by waybill_no, network_type) as network_type_node_num -- 20230821:当前类型节点数,如双集散,network_type=5,network_type_node_num=2
            from (
                select
                   *
                   ,case when network_type =  4 then scan_site_code end as center_code
                from
                   jms_tmp.dwd_tab_barscan_base_dt
                where
                   !(scan_type = 'send' and network_type = 4)
                   and case
                           when network_type = '5' then network_type_rk <= 2
                           else network_type_rk = 1
                       end
             ) a
         ) base
         left join (
                select
                    ship_stop.shipment_no
                    ,ship_stop.sort_num
                    ,ship_stop.network_code        --经停网点编码
                    ,ship_stop.network_name        --经停网点名称
                    ,ship_stop.actual_arrival_time --最晚到车时间
                    ,ship_stop.pre_network_code    --经停网点编码
                    ,ship_stop.pre_network_name    --经停网点名称
                    ,ship.shipment_shifts          --任务班次 20230420新增
                from (
                    select
                        a.shipment_no         as shipment_no
                        ,a.sort_num            as sort_num
                        ,a.network_code        as network_code        --经停网点编码
                        ,a.network_name        as network_name        --经停网点名称
                        ,a.actual_arrival_time as actual_arrival_time --最晚到车时间
                        ,b.network_code        as pre_network_code    --经停网点编码
                        ,b.network_name        as pre_network_name    --经停网点名称
                    from (
                        select *
                        from (
                            select
                                shipment_no --任务号
                                ,sort_num
                                ,network_code --经停网点编码
                                ,network_name --经停网点名称
                                ,actual_arrival_time --最晚到车时间
                                ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                            from jms_dwd.dwd_yl_tmsnew_branch_shipment_stop_base_hi  --支线经停表
                            where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                            and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                        ) a where a.rn = 1
                    ) a
                    left join (
                        select *
                        from (
                            select
                                shipment_no --任务号
                                ,sort_num
                                ,network_code --经停网点编码
                                ,network_name --经停网点名称
                                ,actual_arrival_time --最晚到车时间
                                ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                            from jms_dwd.dwd_yl_tmsnew_branch_shipment_stop_base_hi  --支线经停表
                            where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                            and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                        ) a where a.rn = 1
                    ) b on a.shipment_no = b.shipment_no
                    and a.sort_num -1 = b.sort_num
                    union all
                    select
                        a.shipment_no         as shipment_no
                        ,a.sort_num            as sort_num
                        ,a.network_code        as network_code        --经停网点编码
                        ,a.network_name        as network_name        --经停网点名称
                        ,a.actual_arrival_time as actual_arrival_time --最晚到车时间
                        ,b.network_code        as pre_network_code    --经停网点编码
                        ,b.network_name        as pre_network_name    --经停网点名称
                    from (
                        select *
                        from (
                            select
                                shipment_no --任务号
                                ,sort_num
                                ,network_code --经停网点编码
                                ,network_name --经停网点名称
                                ,actual_arrival_time --最晚到车时间
                                ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                            from jms_dwd.dwd_yl_tmsnew_tms_shipment_stop_base_hf  --支线经停表
                            where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                            and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                        ) a where a.rn = 1
                    ) a
                    left join (
                        select *
                        from (
                            select
                                shipment_no --任务号
                                ,sort_num
                                ,network_code --经停网点编码
                                ,network_name --经停网点名称
                                ,actual_arrival_time --最晚到车时间
                                ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                            from jms_dwd.dwd_yl_tmsnew_tms_shipment_stop_base_hf  --支线经停表
                            where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                            and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                        ) a where a.rn = 1
                    ) b on a.shipment_no = b.shipment_no
                    and a.sort_num -1 = b.sort_num
                ) ship_stop
                left join (
                    select
                        shipment_no
                        ,shipment_shifts
                    from (
                        select
                            shipment_no
                            ,concat(shifts,'班') as shipment_shifts
                            ,row_number() over(partition by shipment_no order by update_time desc) as rn
                        from jms_dwd.dwd_yl_tmsnew_branch_shipment_base_hi
                        where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                        and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                    ) a where a.rn = 1
                    union all
                    select
                        shipment_no
                        ,concat(shifts,'班') as shipment_shifts
                    from jms_dwd.dwd_yl_tmsnew_tms_shipment_base_hf
                    where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                    and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                ) ship on ship_stop.shipment_no = ship.shipment_no
            ) stop on stop.shipment_no = nvl(base.transfer_code,base.waybill_no)     --优先取到件任务号的最晚到车时间
              and stop.network_code = case when base.scan_type in ('send') and  base.next_network_type <> 4 then base.next_station_code --发件，如果不是发往中心就正常用下一站取
                                           when base.scan_type in ('send') then base.next_pre_site_code --发件下一站，实际到件中心
                                           when base.scan_type in ('arrival') and stop.pre_network_code = base.next_station_code then base.scan_site_code end --到件，任务号的上一站，与实际上一站一致才取到件任务号
    ) a group by waybill_no
) branch on base.waybill_no = branch.waybill_no
;


----------------------------第二段：拿出T-2天揽收、入仓数据，取出运单，始发，实际，规划，维度数据
insert overwrite table jms_tmp.dwd_tab_barscan_wdie_base_dt
select
      wide.waybill_no              as waybill_no              --运单号
     ,wide.order_source            as order_source            --订单来源名称
     ,wide.goods_type_code         as goods_type_code         --物品类型编码
     ,wide.goods_type_name         as goods_type_name         --物品类型名称
     ,wide.last_network_code       as last_network_code       --运单表目的网点编码
     ,wide.last_network_name       as last_network_name       --运单表目的网点名称
     ,wide.early_scan_time         as early_scan_time         --最早扫描时间
     ,wide.early_scan_site_code    as early_scan_site_code    --最早的收件网点
     ,base.first_network_code      as first_network_code      --始发网点编码
     ,case when base.first_nodal_time < nvl(base.first_center_time,'2099-12-31 00:00:00') then base.first_nodal_code end as first_nodal_code        --始发集散编码
     ,case when base.first_nodal_time < nvl(base.first_center_time,'2099-12-31 00:00:00') then base.first_nodal_name end as first_nodal_name        --始发集散名称
     ,base.first_center_code       as first_center_code       --始发中心编码
     ,base.first_center_name       as first_center_name       --始发中心名称
     ,base.actual_center_code      as actual_center_code      --实际交件中心编码
     ,base.actual_center_name      as actual_center_name      --实际交件中心名称
     ,base.collect_proxy_code      as collect_proxy_code      --交件代理区编码
     ,base.collect_proxy           as collect_proxy           --交件代理区名称
     ,base.actual_send_time        as actual_send_time        --网点实际发件时间
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.actual_collect_code end as actual_collect_code --实际交件集散点编码
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.actual_collect_name end as actual_collect_name --实际交件集散点名称
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.plan_collect_time   end as plan_collect_time   --规划到达集散时间
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.plan_collect_code   end as plan_collect_code   --规划到达集散编码
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.plan_collect_name   end as plan_collect_name   --规划到达集散名称
     ,case
         when cfg1.id is not null and base.first_nodal_time < nvl(base.first_center_time,'2099-12-31 00:00:00') then base.first_nodal_time
         when base.actual_collect_arr_time < nvl(base.actual_center_arr_time,'2099-12-31 00:00:00') then base.actual_collect_arr_time
      end                                                                                                                       as actual_collect_arr_time --实际交件集散点时间
     ,case
         when cfg1.id is not null and ((unix_timestamp(base.first_center_time) - unix_timestamp(nvl(base.arrival_center_arrival_time, base.send_center_arrival_time))) >= 3 * 3600) then base.first_center_time
         when base.actual_center_arr_time = '2099-12-31 00:00:00' then null
         else base.actual_center_arr_time
     end                                                                                                                        as actual_center_arr_time --实际首中心到车时间
     ,dim.city_id     as receive_city_id           --收件城市id
     ,dim.city_desc   as receive_city              --收件城市
     ,dim.manage_code as region_code               --收件管理大区编码
     ,dim.manage_name as region_name               --收件管理大区名称
     ,dim.agent_code  as receive_proxy_code        --收件代理区编码
     ,dim.agent_name  as receive_proxy             --收件代理区名称
     ,dim.fran_code   as receive_franchisee_code   --收件加盟商编码
     ,dim.fran_name   as receive_franchisee        --收件加盟商名称
     ,wide.early_scan_site_code         as receive_network_code      --收件网点编码
     ,dim.name                          as receive_network_name      --收件网点名称
     ,case when base.collect_proxy_code = dim.agent_code then 1 else 0 end as is_diff_proxy --是否跨代理区交件
     ,substr(wide.early_scan_time,12,8) as scan_time --收件时间
     ,to_date(wide.early_scan_time)  as scan_date --收件日期
     ,base.actual_center_shipment_no as actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
     ,wide.customer_code             as customer_code             --客户编号                 (20220903新增)
     ,wide.express_type_code         as express_type_code         --产品类型编码             (20221103新增)
     ,wide.express_type_name         as express_type_name         --产品类型名称             (20221103新增)
     ,dim_last.area_id               as last_area_code            --目的区/县编码            (20221113新增)
     ,dim_last.area_desc             as last_area_name            --目的区/县名称            (20221113新增)
     ,dim_last.city_id               as last_city_code            --目的城市编码             (20221113新增)
     ,dim_last.city_desc             as last_city_name            --目的城市名称             (20221113新增)
     ,dim_last.agent_code            as last_agent_code           --目的代理区编码           (20221113新增)
     ,dim_last.agent_name            as last_agent_name           --目的代理区名称           (20221113新增)
     ,base.first_nodal_send_time     as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
     ,base.first_nodal_arrival_time  as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
     ,base.first_center_send_time    as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
     ,base.first_center_arrival_time as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
     ,case when wide.if_yiqin = 1 and base.first_center_code is null then 1 else 0 end as if_yiqin_late --疫情滞留(20221213新增)
     ,wide.customer_name             as customer_name --客户名称
     ,base.first_nodal_agent_code    as first_nodal_agent_code --实际交件集散点代理区编码
     ,base.first_nodal_agent_name    as first_nodal_agent_name --实际交件集散点代理区名称
     ,base.first_nodal_fran_code     as first_nodal_fran_code  --实际交件集散点加盟商编码
     ,base.first_nodal_fran_name     as first_nodal_fran_name  --实际交件集散点加盟商名称
     ,base.actual_center_shipment_shifts as actual_center_shipment_shifts--实际交件支线任务班次 20230420
     ,wide.taking_user_code          as taking_user_code --揽收业务员
     ,wide.taking_user_name          as taking_user_name --揽收业务员
     ,base.actual_network_shipment_no as actual_network_shipment_no   -- 网点到件发件任务单
     ,base.actual_nodal_shipment_no   as actual_nodal_shipment_no     -- 集散到件发件任务单
     ,wide.order_mark_en              as order_mark_en  -- 订单标签英文
     ,wide.order_mark_zh              as order_mark_zh  -- 订单标签中文
     ,case when base.second_nodal_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.second_nodal_code end                                as second_nodal_code --实际交件集散点编码
     ,case when base.second_nodal_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.second_nodal_name end                                as second_nodal_name --实际交件集散点名称
     ,case when base.second_nodal_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.second_nodal_time end                                as second_nodal_time -- 第二集散到件发件时间
     ,case when base.second_nodal_send_time    < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.second_nodal_send_time end                   as second_nodal_send_time       -- 第二集散发件时间
     ,case when base.second_nodal_arrival_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.second_nodal_arrival_time  end               as second_nodal_arrival_time    -- 第二集散到件时间
     ,case when base.actual_second_collect_arr_time <  nvl(base.actual_center_arr_time,'2099-12-31 00:00:00') then base.actual_second_collect_arr_time end as actual_second_collect_arr_time -- 实际第二集散到达时间
     ,base.actual_second_nodal_shipment_no                            -- 实际第二集散到件发件任务单
     ,if(cfg1.id is not null, 1, 0)   as car_waybill_split_flag       -- 车件分离标识
     ,if(cfg2.id is not null, 1, 0)   as direct_flag                  -- 直发标识
from (
    select
         wide.*
        ,tak.taking_user_code  --揽收业务员
        ,tak.taking_user_name  --揽收业务员
        ,order_mark.order_mark_en -- 订单标签英文
        ,order_mark.order_mark_zh -- 订单标签中文
    from (
        select
             waybill_no              as waybill_no
            ,goods_type_code         as goods_type_code    --物品类型编码
            ,goods_type_name         as goods_type_name    --物品类型名称
            ,customer_code           as customer_code      --客户编号
            ,customer_name           as customer_name      --客户名称
            ,dispatch_network_code   as last_network_code  --目的网点编码
            ,dispatch_network_name   as last_network_name  --目的网点名称
            ,express_type_code       as express_type_code  --产品类型编码
            ,express_type_name       as express_type_name  --产品类型名称
            ,case when ordersource_name in ('紫金山','逍遥峰','桃花岛','七星潭','桃花岛跨境','桃花岛跨境（逆）','极地湾','紫金山官方代发', '唯品会面单')
                  then ordersource_name else '其他' end as order_source --订单来源名称
            ,least(taking_early_scantime,warehousing_early_scantime) as early_scan_time --最早揽收时间
            ,nvl(taking_early_scansitecode,warehousing_early_scansitecode) as early_scan_site_code --收件网点
            ,case when is_difficult = 1 and difficult_type_code in ('66e','66g') then 1 else 0 end as if_yiqin --疫情滞留(20221213新增)
        from jms_dwd.dwd_wide_unsign_summary_waybill_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',14) and '{{ execution_date | cst_ds }}'
          and to_date(least(taking_early_scantime,warehousing_early_scantime)) between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}' --T-2揽收入仓
          and is_intercept <> 1 --剔除拦截件
          and is_end_piece <> 1 --剔除完结件
    ) wide
    left join (
        select *
        from(
            select
                 waybill_no
                ,scan_site_code
                ,deliver_user_code as taking_user_code --揽收业务员
                ,deliver_user      as taking_user_name --揽收业务员
                ,row_number() over(partition by waybill_no order by scan_time asc) as rn
            from jms_dwd.dwd_tab_barscan_taking_base_dt
            where dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
             and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
        ) a where a.rn = 1
    ) tak on wide.waybill_no = tak.waybill_no
         and wide.early_scan_site_code = nvl(tak.scan_site_code,tak.waybill_no)
    left join ( -- 获取订单标签
        select
            a.waybill_id
            ,a.field_name  as order_mark_en  -- 订单标签英文
            ,b.value       as order_mark_zh  -- 订单标签中文
            ,row_number() over(partition by a.waybill_id order by if(b.value regexp '逆向' or b.value = '裹裹两小时', 1 ,0) desc,a.update_time desc) as rn
        from
            jms_dwd.dwd_yl_oms_order_mark_expand_base_dt a
                left join jms_dim.dim_yl_oms_system_dict_base b on a. field_name = b.label and b.is_delete = 0
        where
            dt between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'
    ) order_mark
        on wide.waybill_no = order_mark.waybill_id and order_mark.rn = 1
    where
       not( nvl(order_mark.order_mark_zh,'*') regexp '逆向' or  nvl(order_mark.order_mark_zh,'*') = '裹裹两小时') -- 剔除订单标签含“逆向”字样的数据和菜鸟裹裹-裹裹两小时
) wide
left join (
    select
          base.*
         ,dim.agent_code as collect_proxy_code
         ,dim.agent_name as collect_proxy
         ,dim_nodal.agent_code as first_nodal_agent_code --代理区编码
         ,dim_nodal.agent_name as first_nodal_agent_name --代理区名称
         ,dim_nodal.fran_code  as first_nodal_fran_code  --加盟商编码
         ,dim_nodal.fran_name  as first_nodal_fran_name  --加盟商名称
    from jms_tmp.dwd_tab_barscan_arrival_send_base_dt base --到件发件合并数据
    left join jms_dim.dim_network_whole_massage dim --网点维度表
       on base.actual_center_code = dim.code
    left join jms_dim.dim_network_whole_massage dim_nodal --取实际交件集散
       on case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00')
               then base.actual_collect_code end = dim_nodal.code
) base on wide.waybill_no = base.waybill_no
left join (
    select
          code
         ,name
         ,city_id
         ,city_desc
         ,manage_code
         ,manage_name
         ,agent_code
         ,agent_name
         ,fran_code
         ,fran_name
         ,network_type
    from jms_dim.dim_network_whole_massage --网点维度表
) dim on wide.early_scan_site_code = dim.code
left join (
    select
          code
         ,name
         ,area_id         --区/县ID
         ,area_desc       --区/县
         ,city_id
         ,city_desc
         ,manage_code
         ,manage_name
         ,agent_code
         ,agent_name
         ,fran_code
         ,fran_name
         ,network_type
    from jms_dim.dim_network_whole_massage --网点维度表
) dim_last on wide.last_network_code = dim_last.code
left join
    bidefault.tab_delivery_white_list_cfg cfg1 -- 车件分离
        on wide.customer_code = cfg1.customer_code
       and wide.early_scan_site_code = cfg1.neteork_code
       and cfg1.type_code = '1'
       and cfg1.status = 1
       and cfg1.dt = '{{ execution_date | cst_ds }}'
left join
    bidefault.tab_delivery_white_list_cfg cfg2 -- 直发
        on wide.early_scan_site_code = cfg2.neteork_code
       and cfg2.type_code = '2'
       and cfg2.status = 1
       and cfg2.dt = '{{ execution_date | cst_ds }}'
;






-------------------------------路由打标
insert overwrite table jms_tmp.dwd_route_for_submit_tmp
select
     in_from_code       --始发网点编码
    ,in_from_name       --始发网点名称
    ,in_collect_code    --始发集散点编码
    ,in_collect_name    --始发集散点名称
    ,extra_in_collect_code -- 第二集散编码
    ,extra_in_collect_name -- 第二集散名称
    ,in_to_code         --始发中心编码
    ,in_to_name         --始发中心名称
    ,out_to_code        --目的网点编码
    ,out_to_name        --目的网点名称
    ,out_from_code      --目的中心编码
    ,out_from_name      --目的中心名称
    ,last_taking_time   --揽收结束时间
    ,in_edge_planned_departure                                          as network_plan_send_time         --始发网点规划发车时间
    ,case when flag in (1, 3, 5) then in_edge_planned_arrival end       as first_nodal_plan_arrival_time  --始发集散规划到达时间
    ,case when flag in (1, 3, 5) then in_edge2_planned_departure end    as first_nodal_plan_send_time     --始发集散规划发车时间
    ,case when flag = 5 then in_edge2_planned_arrival   end             as second_nodal_plan_arrival_time --第二集散规划到达时间
    ,case when flag = 5 then extra_in_planned_departure end             as second_nodal_plan_send_time    --第二集散规划发车时间
    ,case when flag = 1 then in_edge2_planned_arrival
          when flag = 2 then in_edge_planned_arrival
          when flag = 5 then extra_in_planned_arrival
     end                                                                as first_center_plan_arrival_time       --始发中心规划到达时间
    ,case when flag in (1, 2, 5) then e1_planned_departure end          as first_center_plan_send_time          --始发中心规划发车时间
    ,case when flag in (1, 2, 5) then e1_latest_warehousing end         as first_center_plan_latest_arrival_time--始发中心规划最晚到车时间
    ,case when flag = 1 then (reserve_1 + in_line_day + in_edge_span_days)
          when flag = 2 then 0
          when flag = 3 then (reserve_1 + in_line_day)
          when flag = 4 then (reserve_1 + in_line_day)
          when flag = 5 then (reserve_1 + in_line_day + in_edge_span_days)
          end as nodal_span_days  -- 集散跨天
    ,case when flag = 5 then (reserve_1 + in_line_day + in_edge_span_days + in_edge2_stop_days + in_edge2_stop_span_days + in_edge2_span_days)
          else 0
     end      as nodal2_span_days -- 集散2跨天
    ,case when flag = 1 then (reserve_1 + in_line_day + in_edge_span_days + in_edge2_stop_days + in_edge2_stop_span_days + in_edge2_span_days)
          when flag = 2 then (reserve_1 + in_line_day + in_edge_span_days)
          when flag = 3 then 0
          when flag = 4 then (reserve_1 + in_line_day)
          when flag = 5 then (reserve_1 + in_line_day + in_edge_span_days + in_edge2_stop_days + in_edge2_stop_span_days + in_edge2_span_days + extra_in_stop_days + extra_in_stop_span_days + in_edge3_span_days)
     end as center_span_days --中心跨天

    ,reserve_1                      --网点揽收时间-网点收件时间_收件跨天
    ,in_line_day                    --网点收件时间-网点发_衔接跨天
    ,in_edge_span_days              --网点发-集散1到_运输跨天
    ,in_edge2_stop_days             --集散1到-集散1最晚到_衔接跨天
    ,in_edge2_stop_span_days        --集散1最晚到-集散1发_衔接跨天
    ,in_edge2_span_days             --集散1发-集散2到_运输跨天[或]集散1发-中心到_运输跨天
    ,extra_in_stop_days             --集散2到-集散2最晚到_衔接跨天
    ,extra_in_stop_span_days        --集散2最晚到-集散2发_衔接跨天
    ,in_edge3_span_days             --网点-集散-集散-中心场景: 集散2发-中心到_运输跨天
    ,case
        when flag = 1 and in_edge2_planned_arrival > e1_latest_warehousing then 1
        when flag = 2 and in_edge_planned_arrival  > e1_latest_warehousing then 1
        when flag = 5 and extra_in_planned_arrival > e1_latest_warehousing then 1
        when flag in (1, 2, 5) then 0  -- 其他情况置为0, flag非1 2 5 置为为NULL
    end                as  in_edge3_stop_days             -- 中心到-中心最晚到_衔接跨天
    ,case when flag in (1, 2, 5) and e1_latest_warehousing > e1_planned_departure then 1
          when flag in (1, 2, 5) then 0
    end                as  in_edge3_stop_span_days        -- 中心最晚到-中心发_衔接跨天

    ,branch_in_center_span_days     --中心到车1-中心发车1_衔接跨天
    ,e1_end_code                    --第二个中心
    ,total_days_t                   --T+X时效 (20220903新增)
    ,first_center_span_stop_days    --网点-中心-网点场景,补充中心到车与中心发车时间衔接跨天
    ,is_off                         --是否截单
    ,first_warehousing_time         --第一个最晚揽收时间
    ,nvl(lead_warehousing_time,network_lasted_warehousing_time) as lead_warehousing_time  --下一个最晚入仓时间
    ,lasted_warehousing_time        --网点-集散-中心-网点最后一个最晚揽收时间
    ,network_lasted_warehousing_time--网点-网点最后一个最晚揽收时间
    -- ,double_nodal_lasted_warehousing_time -- 网点-集散-集散-中心-网点最后一个最晚揽收时间
    ,network_rk                     --网点-网点去重标识
    ,nodal_rk                       --网点-集散-网点去重标识
    ,center_rk                      --网点-中心-网点去重标识
    ,flag                           --标签
    ,update_date
from(
     select
          in_from_code       --始发网点编码
         ,in_from_name       --始发网点名称
         ,in_collect_code    --始发集散点编码
         ,in_collect_name    --始发集散点名称
         ,extra_in_collect_code -- 第二集散编码
         ,extra_in_collect_name -- 第二集散名称
         ,in_to_code         --始发中心编码
         ,in_to_name         --始发中心名称
         ,out_to_code        --目的网点编码
         ,out_to_name        --目的网点名称
         ,out_from_code      --目的中心编码
         ,out_from_name      --目的中心名称
         ,concat(substr(latest_warehousing_time_quantum,7,5),':00') as last_taking_time           -- 揽收结束时间
        , concat(in_edge_planned_departure   ,':00')  as in_edge_planned_departure                -- 始发网点规划发车时间
        , concat(in_edge_planned_arrival     ,':00')  as in_edge_planned_arrival                  -- 始发网点-下一节点_规划到车时间
        , concat(in_edge2_planned_departure  ,':00')  as in_edge2_planned_departure               -- 首集散-下一节点_规划发车时间
        , concat(in_edge2_planned_arrival    ,':00')  as in_edge2_planned_arrival                 -- 首集散-下一节点_规划到车时间
        , concat(extra_in_planned_departure  ,':00')  as extra_in_planned_departure               -- 第二集散-下一节点_规划发车时间
        , concat(extra_in_planned_arrival    ,':00')  as extra_in_planned_arrival                 -- 第二集散-下一节点_规划到车时间
        , concat(coalesce(e1_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure),':00') as e1_planned_departure --始发中心/末端集散规划发车时间
        -- , concat(e1_planned_departure        ,':00')  as e1_planned_departure                  -- 流向1规划发出时间
        , concat(coalesce(e1_latest_warehousing,out_edge_latest_warehouse_time,out_edge2_latest_warehouse_time),':00') as e1_latest_warehousing -- 中心最晚到车时间
        -- , concat(e1_latest_warehousing       ,':00')  as e1_latest_warehousing                 -- 流向1规划最晚到车时间
        , reserve_1                      --网点揽收时间-网点收件时间_收件跨天
        , in_line_day                    --网点收件时间-网点发_衔接跨天
        , in_edge_span_days              --网点发-集散1到_运输跨天
        , in_edge2_stop_days             --集散1到-集散1最晚到_衔接跨天
        , in_edge2_stop_span_days        --集散1最晚到-集散1发_衔接跨天
        , in_edge2_span_days             --集散1发-集散2到_运输跨天[或]集散1发-中心到_运输跨天
        , extra_in_stop_days             --集散2到-集散2最晚到_衔接跨天
        , extra_in_stop_span_days        --集散2最晚到-集散2发_衔接跨天
        , in_edge3_span_days             --网点-集散-集散-中心场景: 集散2发-中心到_运输跨天
        , branch_in_center_span_days     --中心到1-中心发1_衔接跨天
        , e1_end_code                    --第二个中心
        , total_days_t                   --T+X时效 (20220903新增)
        ,case when coalesce(e1_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) <= nvl(in_edge2_planned_arrival,in_edge_planned_arrival) then 1 else 0 end as first_center_span_stop_days --网点-中心-网点场景,补充中心到车与中心发车时间衔接跨天
        ,if(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,start_network_code order by substr(latest_warehousing_time_quantum,7,5)) between '00:00' and '06:00',0,1) as is_off  --是否截单
        ,concat(lead(substr(latest_warehousing_time_quantum,7,5))        over (partition by dt,in_from_code,in_collect_code,extra_in_collect_code,in_to_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5)),':00')        as lead_warehousing_time   --下一个最晚揽收时间
        ,concat(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,in_collect_code,extra_in_collect_code,in_to_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5)),':00')        as first_warehousing_time  --第一个最晚揽收时间
        ,concat(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,in_collect_code,extra_in_collect_code,in_to_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5) desc),':00')   as lasted_warehousing_time --网点-集散-中心最后一个最晚揽收时间
        ,concat(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,in_collect_code,extra_in_collect_code,in_to_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5) desc),':00')   as network_lasted_warehousing_time --最后一个最晚揽收时间(目前也是按各自的路由算)
        -- ,concat(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5) desc),':00') as network_lasted_warehousing_time --最后一个最晚揽收时间
        -- ,concat(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,in_collect_code,extra_in_collect_code,in_to_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5) desc),':00') as double_nodal_lasted_warehousing_time --网点-双集散-中心-网点最后一个最晚揽收时间
        ,row_number() over(partition by dt,in_from_code,out_to_code                 order by substr(total_days_t,3,2),substr(latest_warehousing_time_quantum,7,5),in_edge_planned_departure,in_edge2_planned_departure,e1_planned_departure) as network_rk      --网点-网点去重标识
        ,row_number() over(partition by dt,in_from_code,in_to_code,out_to_code      order by substr(total_days_t,3,2),substr(latest_warehousing_time_quantum,7,5),in_edge_planned_departure,in_edge2_planned_departure,e1_planned_departure) as center_rk       --网点-中心-网点去重标识
        ,row_number() over(partition by dt,in_from_code,in_collect_code,out_to_code order by substr(total_days_t,3,2),substr(latest_warehousing_time_quantum,7,5),in_edge_planned_departure,in_edge2_planned_departure,e1_planned_departure) as nodal_rk        --网点-集散-网点去重标识
        ,case
               when in_from_code is not null and in_collect_code is not null and extra_in_collect_code is not null and in_to_code is not null then 5  --网点-集散-集散-中心-网点
               when in_from_code is not null and in_collect_code is not null and in_to_code is not null then 1  --网点-集散-中心-网点
               when in_from_code is not null and in_collect_code is null and in_to_code is not null then 2 --网点-中心-网点
               when in_from_code is not null and in_collect_code is not null and in_to_code is null then 3 --网点-集散-网点
               when in_from_code is not null and in_collect_code is null and in_to_code is null then 4 --网点-网点
        end as flag --标签
        ,update_date
     from(
            select
                *
                ,row_number() over(partition by dt, in_from_code,in_collect_code,extra_in_collect_code,in_to_code,out_to_code,substr(latest_warehousing_time_quantum,7,5)
                                        order by search_type asc
                                            ,network_planned_departure  desc --网点1发车时间
                                            ,nodel_f1_planed_departure  desc --集散1发车时间
                                            ,nodel_f2_planed_departure  desc --集散2发车时间
                                            ,center_f1_planed_departure desc --中心1发车时间
                                    ) as rank -- 路由先取时效最快, 再按优先级发车时间最晚的路由
            from (
                    select
                        *
                        ,concat(date_add('{{ execution_date | cst_ds }}',in_line_day),' ',in_edge_planned_departure,':00') as network_planned_departure --网点1发车时间
                        ,concat(date_add('{{ execution_date | cst_ds }}',in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days),' ',in_edge2_planned_departure,':00') as nodel_f1_planed_departure --集散1发车时间
                        ,concat(date_add('{{ execution_date | cst_ds }}',in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days),' ',extra_in_planned_departure,':00') as nodel_f2_planed_departure --集散2发车时间
                        ,concat(date_add('{{ execution_date | cst_ds }}',in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days),' ',e1_planned_departure,':00') as center_f1_planed_departure --中心1发车时间
                    from jms_dm.dm_route_whole_node_base_dt    --新兜底路由
                    where dt between date_sub('{{ execution_date | cst_ds }}',3) and date_sub('{{ execution_date | cst_ds }}',1)
            ) a
     ) a
     where a.rank = 1
) a
;




-----------------------根据始发网点、目的网点，关联兜底路由，拿出路由相关数据，并将三段码问题、规划问题、自收自派数据打标
-----------------------三段码问题、规划问题、自收自派
insert overwrite table jms_tmp.dwd_first_route_for_submit
select
     wide.waybill_no                --运单号
    ,wide.order_source              --订单来源名称
    ,wide.goods_type_code           --物品类型编码
    ,wide.goods_type_name           --物品类型名称
    ,wide.last_network_code         --运单表目的网点编码
    ,wide.last_network_name         --运单表目的网点名称
    ,wide.early_scan_time           --最早扫描时间
    ,wide.early_scan_site_code      --最早的收件网点
    ,wide.first_network_code        --始发网点编码
    ,wide.first_nodal_code          --始发集散编码
    ,wide.first_nodal_name          --始发集散名称
    ,wide.first_center_code         --始发中心编码
    ,wide.first_center_name         --始发中心名称
    ,wide.actual_center_code        --实际交件中心编码
    ,wide.actual_center_name        --实际交件中心名称
    ,wide.collect_proxy_code        --交件代理区编码
    ,wide.collect_proxy             --交件代理区名称
    ,wide.actual_send_time          --网点实际发件时间
    ,wide.actual_collect_code       --实际交件集散点编码
    ,wide.actual_collect_name       --实际交件集散点名称
    ,wide.plan_collect_time         --规划到达集散时间
    ,wide.plan_collect_code         --规划到达集散编码
    ,wide.plan_collect_name         --规划到达集散名称
    ,wide.actual_collect_arr_time   --实际交件集散点时间
    ,wide.actual_center_arr_time    --实际首中心到车时间
    ,wide.receive_city_id           --收件城市id
    ,wide.receive_city              --收件城市
    ,wide.region_code               --收件管理大区编码
    ,wide.region_name               --收件管理大区名称
    ,wide.receive_proxy_code        --收件代理区编码
    ,wide.receive_proxy             --收件代理区名称
    ,wide.receive_franchisee_code   --收件加盟商编码
    ,wide.receive_franchisee        --收件加盟商名称
    ,wide.receive_network_code      --收件网点编码
    ,wide.receive_network_name      --收件网点名称
    ,wide.is_diff_proxy             --是否跨代理区交件
    ,wide.scan_time
    ,wide.scan_date
    ,case when wide.last_network_code is null then 1 --三段码问题:无目的网点
          when wide.early_scan_site_code = wide.last_network_code then 3 --其他问题:自收自派
          when route.in_from_code is null or route.last_taking_time is null then 2 --规划问题:无路由
          else 0 end as type
    ,case when route.is_off = 1 then route.network_lasted_warehousing_time end as truncated_time --截单时间
    ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as is_interrupted  --是否截单
    ,wide.actual_center_shipment_no as actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
    ,wide.customer_code             as customer_code             --客户编号                 (20220903新增)
    ,wide.express_type_code         as express_type_code         --产品类型编码             (20221103新增)
    ,wide.express_type_name         as express_type_name         --产品类型名称             (20221103新增)
    ,wide.last_area_code            as last_area_code            --目的区/县编码            (20221113新增)
    ,wide.last_area_name            as last_area_name            --目的区/县名称            (20221113新增)
    ,wide.last_city_code            as last_city_code            --目的城市编码             (20221113新增)
    ,wide.last_city_name            as last_city_name            --目的城市名称             (20221113新增)
    ,wide.last_agent_code           as last_agent_code           --目的代理区编码           (20221113新增)
    ,wide.last_agent_name           as last_agent_name           --目的代理区名称           (20221113新增)
    ,wide.first_nodal_send_time     as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
    ,wide.first_nodal_arrival_time  as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
    ,wide.first_center_send_time    as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
    ,wide.first_center_arrival_time as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
    ,wide.if_yiqin_late             as if_yiqin_late             --是否疫情件               (20221213新增)
    ,route.total_days_t             as fast_total_days           --网点-网点T+X时效         (20221213新增)
    ,wide.customer_name             as customer_name             --客户名称
    ,wide.first_nodal_agent_code    as first_nodal_agent_code    --实际交件集散点代理区编码
    ,wide.first_nodal_agent_name    as first_nodal_agent_name    --实际交件集散点代理区名称
    ,wide.first_nodal_fran_code     as first_nodal_fran_code     --实际交件集散点加盟商编码
    ,wide.first_nodal_fran_name     as first_nodal_fran_name     --实际交件集散点加盟商名称
    ,wide.actual_center_shipment_shifts as actual_center_shipment_shifts--实际交件支线任务班次 20230420
    ,wide.taking_user_code          as taking_user_code          --揽收业务员 20230516
    ,wide.taking_user_name          as taking_user_name          --揽收业务员 20230516
    ,wide.actual_network_shipment_no  as actual_network_shipment_no   -- 网点到件发件任务单
    ,wide.actual_nodal_shipment_no    as actual_nodal_shipment_no     -- 集散到件发件任务单
    ,wide.order_mark_en               as order_mark_en           -- 订单标签英文
    ,wide.order_mark_zh               as order_mark_zh           -- 订单标签中文
    ,wide.second_nodal_code                                      -- 第二集散code
    ,wide.second_nodal_name                                      -- 第二集散名称
    ,wide.second_nodal_send_time                                 -- 第二集散发件时间
    ,wide.second_nodal_arrival_time                              -- 第二集散到件时间
    ,wide.actual_second_collect_arr_time                         -- 实际第二集散到件时间
    ,wide.actual_second_nodal_shipment_no                        -- 实际第二集散到件发件任务单
    ,wide.car_waybill_split_flag                                 -- 车件分离标识
    ,wide.direct_flag                                            -- 直发标识
from jms_tmp.dwd_tab_barscan_wdie_base_dt wide --揽收数据
left join jms_tmp.dwd_route_for_submit_tmp route
   on wide.early_scan_site_code = route.in_from_code  --按照始发网点,目的网点关联兜底路由数据
  and wide.last_network_code = route.out_to_code
  and route.network_rk = 1
  and wide.scan_date = route.update_date;








-----------------------同城件打标
insert overwrite table jms_tmp.dwd_first_route_city_for_submit
select
     waybill_no                --运单号
    ,order_source              --订单来源名称
    ,goods_type_code           --物品类型编码
    ,goods_type_name           --物品类型名称
    ,last_network_code         --运单表目的网点编码
    ,last_network_name         --运单表目的网点名称
    ,early_scan_time           --最早扫描时间
    ,early_scan_site_code      --最早的收件网点
    ,first_nodal_code          --始发集散编码
    ,first_nodal_name          --始发集散名称
    ,first_center_code         --始发中心编码
    ,first_center_name         --始发中心名称
    ,actual_center_code        --实际交件中心编码
    ,actual_center_name        --实际交件中心名称
    ,collect_proxy_code        --交件代理区编码
    ,collect_proxy             --交件代理区名称
    ,actual_send_time          --网点实际发件时间
    ,actual_collect_code       --实际交件集散点编码
    ,actual_collect_name       --实际交件集散点名称
    ,plan_collect_code         --规划到达集散编码
    ,plan_collect_name         --规划到达集散名称
    ,actual_collect_arr_time   --实际交件集散点时间
    ,actual_center_arr_time    --实际首中心到车时间
    ,receive_city_id           --收件城市id
    ,receive_city              --收件城市
    ,region_code               --收件管理大区编码
    ,region_name               --收件管理大区名称
    ,receive_proxy_code        --收件代理区编码
    ,receive_proxy             --收件代理区名称
    ,receive_franchisee_code   --收件加盟商编码
    ,receive_franchisee        --收件加盟商名称
    ,receive_network_code      --收件网点编码
    ,receive_network_name      --收件网点名称
    ,is_diff_proxy             --是否跨代理区交件
    ,scan_time                 --扫描时间
    ,scan_date                 --扫描日期
    ,max(type)           as type
    ,max(truncated_time) as truncated_time            --截单时间
    ,max(is_interrupted) as is_interrupted            --是否截单
    ,actual_center_shipment_no  --实际首中心到件任务号（20220903新增）
    ,customer_code              --客户编号（20220903新增）
    ,express_type_code         --产品类型编码             (20221103新增)
    ,express_type_name         --产品类型名称             (20221103新增)
    ,last_area_code            --目的区/县编码            (20221113新增)
    ,last_area_name            --目的区/县名称            (20221113新增)
    ,last_city_code            --目的城市编码             (20221113新增)
    ,last_city_name            --目的城市名称             (20221113新增)
    ,last_agent_code           --目的代理区编码           (20221113新增)
    ,last_agent_name           --目的代理区名称           (20221113新增)
    ,first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
    ,first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
    ,first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
    ,first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
    ,if_yiqin_late             --是否疫情件               (20221213新增)
    ,fast_total_days           --网点-网点T+X时效         (20221213新增)
    ,customer_name             --客户名称
    ,first_nodal_agent_code    --实际交件集散点代理区编码
    ,first_nodal_agent_name    --实际交件集散点代理区名称
    ,first_nodal_fran_code     --实际交件集散点加盟商编码
    ,first_nodal_fran_name     --实际交件集散点加盟商名称
    ,actual_center_shipment_shifts--实际交件支线任务班次 20230420新增
    ,taking_user_code          --揽收业务员 20230516
    ,taking_user_name          --揽收业务员 20230516
    ,actual_network_shipment_no  as actual_network_shipment_no   -- 网点到件发件任务单
    ,actual_nodal_shipment_no    as actual_nodal_shipment_no     -- 集散到件发件任务单
    ,order_mark_en             -- 订单标签英文
    ,order_mark_zh             -- 订单标签中文
    ,second_nodal_code                -- 第二集散code
    ,second_nodal_name                -- 第二集散名称
    ,second_nodal_send_time           -- 第二集散发件时间
    ,second_nodal_arrival_time        -- 第二集散到件时间
    ,actual_second_collect_arr_time   -- 实际第二集散到件时间
    ,actual_second_nodal_shipment_no  -- 实际第二集散到件发件任务单
    ,car_waybill_split_flag
    ,direct_flag
from (
    select
          wide.waybill_no                --运单号
         ,wide.order_source              --订单来源名称
         ,wide.goods_type_code           --物品类型编码
         ,wide.goods_type_name           --物品类型名称
         ,wide.last_network_code         --运单表目的网点编码
         ,wide.last_network_name         --运单表目的网点名称
         ,wide.early_scan_time           --最早扫描时间
         ,wide.early_scan_site_code      --最早的收件网点
         ,wide.first_network_code        --始发网点编码
         ,wide.first_nodal_code          --始发集散编码
         ,wide.first_nodal_name          --始发集散名称
         ,wide.first_center_code         --始发中心编码
         ,wide.first_center_name         --始发中心名称
         ,wide.actual_center_code        --实际交件中心编码
         ,wide.actual_center_name        --实际交件中心名称
         ,wide.collect_proxy_code        --交件代理区编码
         ,wide.collect_proxy             --交件代理区名称
         ,wide.actual_send_time          --网点实际发件时间
         ,wide.actual_collect_code       --实际交件集散点编码
         ,wide.actual_collect_name       --实际交件集散点名称
         ,wide.plan_collect_time         --规划到达集散时间
         ,wide.plan_collect_code         --规划到达集散编码
         ,wide.plan_collect_name         --规划到达集散名称
         ,wide.actual_collect_arr_time   --实际交件集散点时间
         ,wide.actual_center_arr_time    --实际首中心到车时间
         ,wide.receive_city_id           --收件城市id
         ,wide.receive_city              --收件城市
         ,wide.region_code               --收件管理大区编码
         ,wide.region_name               --收件管理大区名称
         ,wide.receive_proxy_code        --收件代理区编码
         ,wide.receive_proxy             --收件代理区名称
         ,wide.receive_franchisee_code   --收件加盟商编码
         ,wide.receive_franchisee        --收件加盟商名称
         ,wide.receive_network_code      --收件网点编码
         ,wide.receive_network_name      --收件网点名称
         ,wide.is_diff_proxy             --是否跨代理区交件
         ,wide.scan_time
         ,wide.scan_date
         ,case when route.in_from_code is not null and wide.actual_center_arr_time is null then 4 else 0 end as type --同城件
         ,case when route.is_off = 1 and route.e1_end_code is null then route.last_taking_time end as truncated_time --截单时间
         ,case when route.is_off = 1 and route.e1_end_code is null and wide.scan_time > route.lasted_warehousing_time then 1 else 0 end as is_interrupted  --是否截单
         ,wide.actual_center_shipment_no as actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
         ,wide.customer_code             as customer_code             --客户编号                 (20220903新增)
         ,wide.express_type_code         as express_type_code         --产品类型编码             (20221103新增)
         ,wide.express_type_name         as express_type_name         --产品类型名称             (20221103新增)
         ,wide.last_area_code            as last_area_code            --目的区/县编码            (20221113新增)
         ,wide.last_area_name            as last_area_name            --目的区/县名称            (20221113新增)
         ,wide.last_city_code            as last_city_code            --目的城市编码             (20221113新增)
         ,wide.last_city_name            as last_city_name            --目的城市名称             (20221113新增)
         ,wide.last_agent_code           as last_agent_code           --目的代理区编码           (20221113新增)
         ,wide.last_agent_name           as last_agent_name           --目的代理区名称           (20221113新增)
         ,wide.first_nodal_send_time     as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
         ,wide.first_nodal_arrival_time  as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
         ,wide.first_center_send_time    as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
         ,wide.first_center_arrival_time as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
         ,wide.if_yiqin_late             as if_yiqin_late             --是否疫情件               (20221213新增)
         ,wide.fast_total_days           as fast_total_days           --网点-网点T+X时效         (20221213新增)
         ,wide.customer_name             as customer_name             --客户名称
         ,wide.first_nodal_agent_code    as first_nodal_agent_code    --实际交件集散点代理区编码
         ,wide.first_nodal_agent_name    as first_nodal_agent_name    --实际交件集散点代理区名称
         ,wide.first_nodal_fran_code     as first_nodal_fran_code     --实际交件集散点加盟商编码
         ,wide.first_nodal_fran_name     as first_nodal_fran_name     --实际交件集散点加盟商名称
         ,wide.actual_center_shipment_shifts as actual_center_shipment_shifts--实际交件支线任务班次 20230420新增
         ,wide.taking_user_code          as taking_user_code --揽收业务员 20230516
         ,wide.taking_user_name          as taking_user_name --揽收业务员 20230516
         ,wide.actual_network_shipment_no  as actual_network_shipment_no   -- 网点到件发件任务单
         ,wide.actual_nodal_shipment_no    as actual_nodal_shipment_no     -- 集散到件发件任务单
         ,wide.order_mark_en               as order_mark_en  -- 订单标签英文
         ,wide.order_mark_zh               as order_mark_zh  -- 订单标签中文
         ,wide.second_nodal_code                                      -- 第二集散code
         ,wide.second_nodal_name                                      -- 第二集散名称
         ,wide.second_nodal_send_time                                 -- 第二集散发件时间
         ,wide.second_nodal_arrival_time                              -- 第二集散到件时间
         ,wide.actual_second_collect_arr_time                         -- 实际第二集散到件时间
         ,wide.actual_second_nodal_shipment_no                        -- 实际第二集散到件发件任务单
         ,wide.car_waybill_split_flag                                 -- 车件分离标识
         ,wide.direct_flag                                            -- 直发标识
     from (
        select *
        from jms_tmp.dwd_first_route_for_submit
        where type = 0 --剔除三种异常件
    ) wide
    left join (
        select *
        from jms_tmp.dwd_route_for_submit_tmp  --兜底路由 始发网点、始发集散、始发中心、目的中心
        where e1_end_code is null
    ) route on wide.early_scan_site_code = route.in_from_code  --按照始发网点,目的网点关联兜底路由数据
           and wide.last_network_code = route.out_to_code
           and wide.scan_date = route.update_date
) a group by waybill_no                --运单号
            ,order_source              --订单来源名称
            ,goods_type_code           --物品类型编码
            ,goods_type_name           --物品类型名称
            ,last_network_code         --运单表目的网点编码
            ,last_network_name         --运单表目的网点名称
            ,early_scan_time           --最早扫描时间
            ,early_scan_site_code      --最早的收件网点
            ,first_network_code        --始发网点编码
            ,first_nodal_code          --始发集散编码
            ,first_nodal_name          --始发集散名称
            ,first_center_code         --始发中心编码
            ,first_center_name         --始发中心名称
            ,actual_center_code        --实际交件中心编码
            ,actual_center_name        --实际交件中心名称
            ,collect_proxy_code        --交件代理区编码
            ,collect_proxy             --交件代理区名称
            ,actual_send_time          --网点实际发件时间
            ,actual_collect_code       --实际交件集散点编码
            ,actual_collect_name       --实际交件集散点名称
            ,plan_collect_time         --规划到达集散时间
            ,plan_collect_code         --规划到达集散编码
            ,plan_collect_name         --规划到达集散名称
            ,actual_collect_arr_time   --实际交件集散点时间
            ,actual_center_arr_time    --实际首中心到车时间
            ,receive_city_id           --收件城市id
            ,receive_city              --收件城市
            ,region_code               --收件管理大区编码
            ,region_name               --收件管理大区名称
            ,receive_proxy_code        --收件代理区编码
            ,receive_proxy             --收件代理区名称
            ,receive_franchisee_code   --收件加盟商编码
            ,receive_franchisee        --收件加盟商名称
            ,receive_network_code      --收件网点编码
            ,receive_network_name      --收件网点名称
            ,is_diff_proxy             --是否跨代理区交件
            ,scan_time                 --扫描时间
            ,scan_date                 --扫描日期
            ,actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
            ,customer_code             --客户编号                 (20220903新增)
            ,express_type_code         --产品类型编码             (20221103新增)
            ,express_type_name         --产品类型名称             (20221103新增)
            ,last_area_code            --目的区/县编码            (20221113新增)
            ,last_area_name            --目的区/县名称            (20221113新增)
            ,last_city_code            --目的城市编码             (20221113新增)
            ,last_city_name            --目的城市名称             (20221113新增)
            ,last_agent_code           --目的代理区编码           (20221113新增)
            ,last_agent_name           --目的代理区名称           (20221113新增)
            ,first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
            ,first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
            ,first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
            ,first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
            ,if_yiqin_late             --是否疫情件               (20221213新增)
            ,fast_total_days           --网点-网点T+X时效         (20221213新增)
            ,customer_name             --客户名称
            ,first_nodal_agent_code    --实际交件集散点代理区编码
            ,first_nodal_agent_name    --实际交件集散点代理区名称
            ,first_nodal_fran_code     --实际交件集散点加盟商编码
            ,first_nodal_fran_name     --实际交件集散点加盟商名称
            ,actual_center_shipment_shifts --实际首中心任务班次
            ,taking_user_code         --揽收业务员 20230516
            ,taking_user_name         --揽收业务员 20230516
            ,actual_network_shipment_no -- 网点到件发件任务单
            ,actual_nodal_shipment_no   -- 集散到件发件任务单
            ,order_mark_en            -- 订单标签英文
            ,order_mark_zh            -- 订单标签中文
            ,second_nodal_code                 -- 第二集散code
            ,second_nodal_name                 -- 第二集散名称
            ,second_nodal_send_time            -- 第二集散发件时间
            ,second_nodal_arrival_time         -- 第二集散到件时间
            ,actual_second_collect_arr_time    -- 实际第二集散到件时间
            ,actual_second_nodal_shipment_no   -- 实际第二集散到件发件任务单
            ,car_waybill_split_flag
            ,direct_flag
;









-------------------------------第二段\根据始发网点、始发集散、始发中心、目的网点 关联兜底路由，拿出路由相关数据，并将错发数据打标
-------------------------------错发
insert overwrite table jms_tmp.dwd_second_error_route_for_submit
select
      wide.waybill_no                --运单号
     ,wide.order_source              --订单来源名称
     ,wide.goods_type_code           --物品类型编码
     ,wide.goods_type_name           --物品类型名称
     ,wide.last_network_code         --运单表目的网点编码
     ,wide.last_network_name         --运单表目的网点名称
     ,wide.early_scan_time           --最早扫描时间
     ,wide.early_scan_site_code      --最早扫描时间
     ,wide.first_nodal_code          --始发集散编码
     ,wide.first_nodal_name          --始发集散名称
     ,wide.first_center_code         --始发中心编码
     ,wide.first_center_name         --始发中心名称
     ,wide.actual_collect_code       --实际交件集散点编码
     ,wide.actual_collect_name       --实际交件集散点名称
     ,wide.actual_center_code        --实际交件中心编码
     ,wide.actual_center_name        --实际交件中心名称
     ,wide.collect_proxy_code        --交件代理区编码
     ,wide.collect_proxy             --交件代理区名称
     ,wide.actual_send_time          --网点实际发件时间
     ,wide.plan_collect_code         --规划到达集散编码
     ,wide.plan_collect_name         --规划到达集散名称
     ,wide.actual_collect_arr_time   --实际交件集散点时间
     ,wide.actual_center_arr_time    --实际首中心到车时间
     ,wide.receive_city_id           --收件城市id
     ,wide.receive_city              --收件城市
     ,wide.region_code               --收件管理大区编码
     ,wide.region_name               --收件管理大区名称
     ,wide.receive_proxy_code        --收件代理区编码
     ,wide.receive_proxy             --收件代理区名称
     ,wide.receive_franchisee_code   --收件加盟商编码
     ,wide.receive_franchisee        --收件加盟商名称
     ,wide.receive_network_code      --收件网点编码
     ,wide.receive_network_name      --收件网点名称
     ,wide.is_diff_proxy             --是否跨代理区
     ,wide.scan_time
     ,wide.scan_date
     ,case when wide.early_scan_site_code is not null and wide.first_nodal_code is null and wide.first_center_code is null then 0 --网点后无操作不定位为错发，直接判责
           when wide.early_scan_site_code is not null and wide.first_nodal_code is not null and wide.first_center_code is null then 0 --网点-集散后无操作不定位为错发，直接判责
           when route.in_from_code is null then 5
           else wide.type end as type --无路由:错发
     ,case when route.is_off = 1 then route.network_lasted_warehousing_time  end as truncated_time --截单时间
     ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as is_interrupted  --是否截单
     ,wide.actual_center_shipment_no as actual_center_shipment_no --实际首中心到件任务号(20220903新增)
     ,wide.customer_code             as customer_code             --客户编号            (20220903新增)
     ,wide.express_type_code         as express_type_code         --产品类型编码        (20221103新增)
     ,wide.express_type_name         as express_type_name         --产品类型名称        (20221103新增)
     ,wide.last_area_code            as last_area_code            --目的区/县编码       (20221113新增)
     ,wide.last_area_name            as last_area_name            --目的区/县名称       (20221113新增)
     ,wide.last_city_code            as last_city_code            --目的城市编码        (20221113新增)
     ,wide.last_city_name            as last_city_name            --目的城市名称        (20221113新增)
     ,wide.last_agent_code           as last_agent_code           --目的代理区编码      (20221113新增)
     ,wide.last_agent_name           as last_agent_name           --目的代理区名称      (20221113新增)
     ,wide.first_nodal_send_time     as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
     ,wide.first_nodal_arrival_time  as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
     ,wide.first_center_send_time    as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
     ,wide.first_center_arrival_time as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
     ,wide.if_yiqin_late             as if_yiqin_late             --是否疫情件               (20221213新增)
     ,wide.fast_total_days           as fast_total_days           --网点-网点T+X时效         (20221213新增)
     ,wide.customer_name             as customer_name             --客户名称
     ,wide.first_nodal_agent_code    as first_nodal_agent_code    --实际交件集散点代理区编码
     ,wide.first_nodal_agent_name    as first_nodal_agent_name    --实际交件集散点代理区名称
     ,wide.first_nodal_fran_code     as first_nodal_fran_code     --实际交件集散点加盟商编码
     ,wide.first_nodal_fran_name     as first_nodal_fran_name     --实际交件集散点加盟商名称
     ,wide.actual_center_shipment_shifts as actual_center_shipment_shifts--实际交件支线任务班次 20230420新增
     ,wide.taking_user_code          as taking_user_code --揽收业务员 20230516
     ,wide.taking_user_name          as taking_user_name --揽收业务员 20230516
     ,wide.actual_network_shipment_no  as actual_network_shipment_no   -- 网点到件发件任务单
     ,wide.actual_nodal_shipment_no    as actual_nodal_shipment_no     -- 集散到件发件任务单
     ,wide.order_mark_en               as order_mark_en -- 订单标签英文
     ,wide.order_mark_zh               as order_mark_zh -- 订单标签中文
     ,wide.second_nodal_code                                      -- 第二集散code
     ,wide.second_nodal_name                                      -- 第二集散名称
     ,wide.second_nodal_send_time                                 -- 第二集散发件时间
     ,wide.second_nodal_arrival_time                              -- 第二集散到件时间
     ,wide.actual_second_collect_arr_time                         -- 实际第二集散到件时间
     ,wide.actual_second_nodal_shipment_no                        -- 实际第二集散到件发件任务单
     ,wide.car_waybill_split_flag
     ,wide.direct_flag
from (
    select *
    from jms_tmp.dwd_first_route_city_for_submit
    where type = 0   --有始发网点和目的网点
) wide
left join jms_tmp.dwd_route_for_submit_tmp route --兜底路由 始发网点、始发集散、始发中心、目的中心
   on wide.early_scan_site_code = route.in_from_code  --按照始发网点、始发中心、目的网点关联兜底路由数据
  and nvl(wide.first_center_code,0) = nvl(route.in_to_code,0)
  and wide.last_network_code = route.out_to_code
  and route.center_rk = 1
  and wide.scan_date = route.update_date
;






-------------------------------不打标，计算集散、中心规划到达时间
-- 网点-集散-网点场景
-- insert overwrite table jms_tmp.dwd_collect_route_for_submit
-- select
--       waybill_no                --运单号
--      ,order_source              --订单来源名称
--      ,goods_type_code           --物品类型编码
--      ,goods_type_name           --物品类型名称
--      ,last_network_code         --运单表目的网点编码
--      ,last_network_name         --运单表目的网点名称
--      ,early_scan_time           --最早扫描时间
--      ,early_scan_site_code      --最早扫描时间
--      ,first_nodal_code          --始发集散编码
--      ,first_nodal_name          --始发集散名称
--      ,first_center_code         --始发中心编码
--      ,first_center_name         --始发中心名称
--      ,actual_collect_code       --实际交件集散点编码
--      ,actual_collect_name       --实际交件集散点名称
--      ,actual_center_code        --实际交件中心编码
--      ,actual_center_name        --实际交件中心名称
--      ,collect_proxy_code        --交件代理区编码
--      ,collect_proxy             --交件代理区名称
--      ,actual_send_time          --网点实际发件时间
--      ,plan_collect_code         --规划到达集散编码
--      ,plan_collect_name         --规划到达集散名称
--      ,actual_collect_arr_time   --实际交件集散点时间
--      ,null as actual_center_arr_time    --实际首中心到车时间
--      ,receive_city_id           --收件城市id
--      ,receive_city              --收件城市
--      ,region_code               --收件管理大区编码
--      ,region_name               --收件管理大区名称
--      ,receive_proxy_code        --收件代理区编码
--      ,receive_proxy             --收件代理区名称
--      ,receive_franchisee_code   --收件加盟商编码
--      ,receive_franchisee        --收件加盟商名称
--      ,receive_network_code      --收件网点编码
--      ,receive_network_name      --收件网点名称
--      ,is_diff_proxy             --是否跨代理区
--      ,scan_time                 --揽收时间
--      ,scan_date                 --揽收日期
--      ,type                      --
--      ,route_truncated_time as truncated_time  --截单时间
--      ,route_is_interrupted as is_interrupted  --是否截单
--      ,plan_collect_arr_time            --始发集散规划到达时间
--      ,plan_center_arr_time             --始发中心规划到达时间
--      ,actual_center_shipment_no        --实际首中心到件任务号     (20220903新增)
--      ,customer_code                    --客户编号                 (20220903新增)
--      ,total_days_t                     --T+x时效                  (20220903新增)
--      ,null as center_planned_departure --中心规划发车时间-1小时   (20220903新增)
--      ,null as is_intime                --时效是否准点             (20220903新增)
--      ,express_type_code                --产品类型编码             (20221103新增)
--      ,express_type_name                --产品类型名称             (20221103新增)
--      ,last_area_code                   --目的区/县编码            (20221113新增)
--      ,last_area_name                   --目的区/县名称            (20221113新增)
--      ,last_city_code                   --目的城市编码             (20221113新增)
--      ,last_city_name                   --目的城市名称             (20221113新增)
--      ,last_agent_code                  --目的代理区编码           (20221113新增)
--      ,last_agent_name                  --目的代理区名称           (20221113新增)
--      ,first_nodal_send_time            --始发集散发件扫描时间     (20221113新增)
--      ,first_nodal_arrival_time         --始发集散到件扫描时间     (20221113新增)
--      ,first_center_send_time           --始发转运中心发件扫描时间 (20221113新增)
--      ,first_center_arrival_time        --始发转运中心到件扫描时间 (20221113新增)
--      ,if_yiqin_late                    --是否疫情件               (20221213新增)
--      ,fast_total_days                  --网点-网点T+X时效         (20221213新增)
--      ,null as first_center_plan_send_time --始发中心规划发车时间
--      ,customer_name                    --客户名称
--      ,first_nodal_agent_code           --实际交件集散点代理区编码
--      ,first_nodal_agent_name           --实际交件集散点代理区名称
--      ,first_nodal_fran_code            --实际交件集散点加盟商编码
--      ,first_nodal_fran_name            --实际交件集散点加盟商名称
--      ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
--      ,taking_user_code                 --揽收业务员 20230516
--      ,taking_user_name                 --揽收业务员 20230516
--      ,actual_network_shipment_no       --网点到件发件任务单
--      ,actual_nodal_shipment_no         --集散到件发件任务单
--      ,first_network_plan_send_time     --始发网点规划发车时间
--      ,first_nodal_plan_send_time       --始发集散规划发车时间
--      ,order_mark_en               as order_mark_en -- 订单标签英文
--      ,order_mark_zh               as order_mark_zh -- 订单标签中文
--      ,second_nodal_code                -- 第二集散code
--      ,second_nodal_name                -- 第二集散名称
--      ,second_nodal_send_time           -- 第二集散发件时间
--      ,second_nodal_arrival_time        -- 第二集散到件时间
--      ,actual_second_collect_arr_time   -- 实际第二集散到件时间
--      ,actual_second_nodal_shipment_no  -- 实际第二集散到件发件任务单
--      ,null as second_nodal_plan_arrival_time   --第二集散规划到件时间
--      ,null as second_nodal_plan_send_time      --第二集散规划发件时间
-- from (
--     select *,row_number() over(partition by waybill_no order by data_num asc) as rn
--     from (
--          select
--                wide.*
--               ,route.total_days_t as total_days_t --T+x时效
--               ,concat(date_add(wide.scan_date,route.reserve_1 + route.in_line_day + if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.network_plan_send_time)              as first_network_plan_send_time -- 网点规划发车时间
--               ,concat(date_add(wide.scan_date,route.nodal_span_days + route.in_edge2_stop_days + in_edge2_stop_span_days + if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_send_time)  as first_nodal_plan_send_time   -- 集散规划发车时间
--               ,case when route.is_off = 1 then route.network_lasted_warehousing_time end as route_truncated_time --截单时间
--               ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as route_is_interrupted  --是否截单
--               ,concat(date_add(wide.scan_date,nodal_span_days+if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_arrival_time) as plan_collect_arr_time --始发集散规划到达时间
--               ,null as plan_center_arr_time --始发中心规划到达时间
--               -- data_num 大于最晚揽收时间: 第二天0点-6点有路由就取第二天最早, 没有就取当天最晚路由
--               -- data_num 小于最晚揽收时间: 最扫描时间最近一条路由
--               ,case when wide.scan_time > route.network_lasted_warehousing_time then unix_timestamp(concat(date_add(wide.scan_date,1),' ',
--                         if(route.last_taking_time >= '00:00:00' and route.last_taking_time <= '06:00:00',route.first_warehousing_time,
--                         if(route.last_taking_time = route.network_lasted_warehousing_time,route.last_taking_time,null)))) - unix_timestamp(wide.early_scan_time)
--                     when nvl(unix_timestamp(concat(wide.scan_date,' ',route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time) >0
--                     then nvl(unix_timestamp(concat(wide.scan_date,' ',route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time)
--                     else 99999999999999999
--                     end as data_num
--          from (
--               select *
--               from jms_tmp.dwd_second_error_route_for_submit
--               where type = 0  --剔除错发数据
--                 and early_scan_site_code is not null
--                 and first_nodal_code is not null
--                 and first_center_code is  null
--          ) wide
--          left join jms_tmp.dwd_route_for_submit_tmp route
--             on wide.early_scan_site_code = route.in_from_code  --按照始发网点、始发集散、始发中心、目的网点关联兜底路由数据
--            and nvl(wide.first_nodal_code ,0) = nvl(route.in_collect_code,0)
--            and wide.last_network_code = route.out_to_code
--            and wide.scan_date = route.update_date
--     ) t where data_num >0
-- ) a where a.rn = 1;


-------------------------------不打标，计算集散、中心规划到达时间
insert overwrite table jms_tmp.dwd_three_route_for_submit
select
      waybill_no                --运单号
     ,order_source              --订单来源名称
     ,goods_type_code           --物品类型编码
     ,goods_type_name           --物品类型名称
     ,last_network_code         --运单表目的网点编码
     ,last_network_name         --运单表目的网点名称
     ,early_scan_time           --最早扫描时间
     ,early_scan_site_code      --最早扫描时间
     ,first_nodal_code          --始发集散编码
     ,first_nodal_name          --始发集散名称
     ,first_center_code         --始发中心编码
     ,first_center_name         --始发中心名称
     ,actual_collect_code       --实际交件集散点编码
     ,actual_collect_name       --实际交件集散点名称
     ,actual_center_code        --实际交件中心编码
     ,actual_center_name        --实际交件中心名称
     ,collect_proxy_code        --交件代理区编码
     ,collect_proxy             --交件代理区名称
     ,actual_send_time          --网点实际发件时间
     ,plan_collect_code         --规划到达集散编码
     ,plan_collect_name         --规划到达集散名称
     ,actual_collect_arr_time   --实际交件集散点时间
     ,actual_center_arr_time    --实际首中心到车时间
     ,receive_city_id           --收件城市id
     ,receive_city              --收件城市
     ,region_code               --收件管理大区编码
     ,region_name               --收件管理大区名称
     ,receive_proxy_code        --收件代理区编码
     ,receive_proxy             --收件代理区名称
     ,receive_franchisee_code   --收件加盟商编码
     ,receive_franchisee        --收件加盟商名称
     ,receive_network_code      --收件网点编码
     ,receive_network_name      --收件网点名称
     ,is_diff_proxy             --是否跨代理区
     ,scan_time                 --揽收时间
     ,scan_date                 --揽收日期
     -- 需求描述:将T+2日上午7点未到达首个转运中心(首中心到达时间为空2),以兜底路由进行再次判断,
     -- 兜底路由规划到达时间<T+2日7点判断为"晚点",兜底路由规划到达时间>=T+2日7点判断为"未到交件时间
     -- 网点-网点, 网点-(双)集散-网点 路由都要与对应的路由匹配
     -- 在进出港白名单中网点直发名单, 截止7点首中心到达时间为空,集散不为空,未到交件时间
     ,case when actual_center_arr_time is null and
                plan_center_arr_time >= concat(date_add(to_date(early_scan_time),2),' 07:00:00') then 6
           when actual_center_arr_time is null and direct_flag = 1 and first_nodal_code is not null then 6
           else 0
     end   as type --20231225: 类型未到交接时间
     -- ,type
     ,route_truncated_time as truncated_time  --截单时间
     ,route_is_interrupted as is_interrupted  --是否截单
     ,plan_collect_arr_time       --始发集散规划到达时间
     ,plan_center_arr_time        --始发中心规划到达时间
     ,actual_center_shipment_no   --实际首中心到件任务号     (20220903新增)
     ,customer_code               --客户编号                 (20220903新增）
     ,total_days_t                --T+x时效                  (20220903新增)
     ,center_planned_departure    --中心规划发车时间-1小时   (20220903新增)
     ,is_intime                   --时效是否准点             (20220903新增)
     ,express_type_code           --产品类型编码             (20221103新增)
     ,express_type_name           --产品类型名称             (20221103新增)
     ,last_area_code              --目的区/县编码            (20221113新增)
     ,last_area_name              --目的区/县名称            (20221113新增)
     ,last_city_code              --目的城市编码             (20221113新增)
     ,last_city_name              --目的城市名称             (20221113新增)
     ,last_agent_code             --目的代理区编码           (20221113新增)
     ,last_agent_name             --目的代理区名称           (20221113新增)
     ,first_nodal_send_time       --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time    --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time      --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time   --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late               --是否疫情件               (20221213新增)
     ,fast_total_days             --网点-网点T+X时效         (20221213新增)
     ,first_center_plan_send_time --始发中心规划发车时间     (20230103新增)
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       --网点到件发件任务单
     ,actual_nodal_shipment_no         --集散到件发件任务单
     ,first_network_plan_send_time     --始发网点规划发车时间
     ,first_nodal_plan_send_time       --始发集散规划发车时间
     ,order_mark_en                    --订单标签英文
     ,order_mark_zh                    --订单标签中文
     ,second_nodal_code                --第二集散code
     ,second_nodal_name                --第二集散名称
     ,second_nodal_send_time           --第二集散发件时间
     ,second_nodal_arrival_time        --第二集散到件时间
     ,actual_second_collect_arr_time   --实际第二集散到件时间
     ,actual_second_nodal_shipment_no  --实际第二集散到件发件任务单
     ,second_nodal_plan_arrival_time   --第二集散规划到件时间
     ,second_nodal_plan_send_time      --第二集散规划发件时间
     ,car_waybill_split_flag
     ,direct_flag
from (
     select
           *
          ,case when actual_center_arr_time <= center_planned_departure then 1 else 0 end as is_intime --时效是否准点（20220903新增）
          ,row_number() over(partition by waybill_no order by data_num asc) as rn
     from (
          select
                wide.*
               ,route.total_days_t as total_days_t --T+x时效
               ,from_unixtime(unix_timestamp(concat(date_add(wide.scan_date,route.center_span_days + route.in_edge3_stop_days + route.in_edge3_stop_span_days),' ',route.first_center_plan_send_time))-3600,'yyyy-MM-dd HH:mm:ss') as center_planned_departure --中心规划发车时间-1小时（20220903新增）
               ,concat(date_add(wide.scan_date,route.center_span_days + route.in_edge3_stop_days + route.in_edge3_stop_span_days),' ',route.first_center_plan_send_time) as first_center_plan_send_time --始发中心规划发车时间
               ,concat(date_add(wide.scan_date,route.reserve_1 + route.in_line_day + if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.network_plan_send_time)             as first_network_plan_send_time -- 始发网点规划发车时间
               ,concat(date_add(wide.scan_date,route.nodal_span_days + route.in_edge2_stop_days + route.in_edge2_stop_span_days + if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_send_time)    as first_nodal_plan_send_time    -- 始发集散规划发车时间
               ,concat(date_add(wide.scan_date,route.nodal2_span_days + route.extra_in_stop_days + route.extra_in_stop_span_days + if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.second_nodal_plan_send_time)  as second_nodal_plan_send_time   -- 始发集散2规划发车时间
               ,case when route.is_off = 1 then route.network_lasted_warehousing_time end as route_truncated_time --截单时间
               ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as route_is_interrupted  --是否截单
               ,concat(date_add(wide.scan_date,route.nodal_span_days+if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_arrival_time)    as plan_collect_arr_time             --始发集散规划到达时间
               ,concat(date_add(wide.scan_date,route.nodal2_span_days+if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)) ,' ',route.second_nodal_plan_arrival_time) as second_nodal_plan_arrival_time    --第二集散规划到达时间
               ,case
                     when wide.car_waybill_split_flag = 1 then concat(date_add(wide.scan_date,route.center_span_days + if(route.branch_in_center_span_days <> 0,route.branch_in_center_span_days,route.in_edge3_stop_days)),' ',route.first_center_plan_latest_arrival_time) -- 如果在车件分离配置中，取最晚到车时间
                     when wide.scan_time < route.first_warehousing_time --揽收时间<最早的最晚入仓时间
                         then concat(date_add(wide.scan_date,if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)+route.center_span_days),' ',route.first_center_plan_arrival_time) --无截单补充揽收跨天
                     when wide.scan_time >=route.first_warehousing_time and wide.scan_time <= route.lead_warehousing_time --揽收时间<下一个最晚入仓时间
                         then concat(date_add(wide.scan_date,if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)+route.center_span_days),' ',route.first_center_plan_arrival_time) --无截单补充揽收跨天
                     when wide.scan_time > route.network_lasted_warehousing_time --揽收时间>最晚的最晚入仓时间
                         then (case when route.is_off = 0 then concat(date_add(wide.scan_date,route.center_span_days+1),' ',route.first_center_plan_arrival_time) --揽收时间 大于 最晚的最晚入仓时间，且无截单，正常取，直接+1天
                                    else concat(date_add(wide.scan_date,route.center_span_days + if(route.branch_in_center_span_days <> 0,route.branch_in_center_span_days,route.in_edge3_stop_days)),' ',route.first_center_plan_latest_arrival_time)   --有截单，用全链路判责最晚到车时间
                         end)
                     end as plan_center_arr_time --始发中心规划到达时间
               ,case when wide.scan_time > route.network_lasted_warehousing_time
                         then unix_timestamp(concat(date_add(wide.scan_date,1),' ',
                         if(route.last_taking_time >= '00:00:00' and route.last_taking_time <= '06:00:00',route.first_warehousing_time,
                         if(route.last_taking_time = route.network_lasted_warehousing_time,route.last_taking_time,null)))) - unix_timestamp(wide.early_scan_time)
                     when nvl(unix_timestamp(concat(wide.scan_date,' ', route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time) >0
                         then nvl(unix_timestamp(concat(wide.scan_date,' ',route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time)
                     else 99999999999999999
                     end as data_num
          from (
               select *
               from jms_tmp.dwd_second_error_route_for_submit
               where type = 0  --剔除错发数据
                 -- and !(early_scan_site_code is not null
                 -- and first_nodal_code is not null
                 -- and first_center_code is  null)
          ) wide
          left join jms_tmp.dwd_route_for_submit_tmp route
             on wide.early_scan_site_code = route.in_from_code  --按照始发网点、始发集散、始发中心、目的网点关联兜底路由数据
            and nvl(wide.first_nodal_code ,0) = nvl(route.in_collect_code,0)
            and nvl(wide.second_nodal_code,0) = nvl(route.extra_in_collect_code,0)
            and
                case when wide.first_center_code is null then 1=1
                     else wide.first_center_code = route.in_to_code
                end
            -- and nvl(wide.first_center_code,0) = nvl(route.in_to_code     ,0)
            and wide.last_network_code = route.out_to_code
            and wide.scan_date = route.update_date
     ) t where data_num >0
) a where a.rn = 1;






-------------------------------不打标，计算集散、中心规划到达时间
insert overwrite table jms_tmp.dwd_four_route_for_submit
select
      waybill_no                  --运单号
     ,order_source                --订单来源名称
     ,goods_type_code             --物品类型编码
     ,goods_type_name             --物品类型名称
     ,last_network_code           --运单表目的网点编码
     ,last_network_name           --运单表目的网点名称
     ,early_scan_time             --最早扫描时间
     ,early_scan_site_code        --最早扫描时间
     ,first_nodal_code            --始发集散编码
     ,first_nodal_name            --始发集散名称
     ,first_center_code           --始发中心编码
     ,first_center_name           --始发中心名称
     ,actual_collect_code         --实际交件集散点编码
     ,actual_collect_name         --实际交件集散点名称
     ,actual_center_code          --实际交件中心编码
     ,actual_center_name          --实际交件中心名称
     ,collect_proxy_code          --交件代理区编码
     ,collect_proxy               --交件代理区名称
     ,actual_send_time            --网点实际发件时间
     ,plan_collect_code           --规划到达集散编码
     ,plan_collect_name           --规划到达集散名称
     ,actual_collect_arr_time     --实际交件集散点时间
     ,actual_center_arr_time      --实际首中心到车时间
     ,receive_city_id             --收件城市id
     ,receive_city                --收件城市
     ,region_code                 --收件管理大区编码
     ,region_name                 --收件管理大区名称
     ,receive_proxy_code          --收件代理区编码
     ,receive_proxy               --收件代理区名称
     ,receive_franchisee_code     --收件加盟商编码
     ,receive_franchisee          --收件加盟商名称
     ,receive_network_code        --收件网点编码
     ,receive_network_name        --收件网点名称
     ,is_diff_proxy               --是否跨代理区
     ,scan_time                   --揽收时间
     ,scan_date                   --揽收日期
     ,case when actual_center_arr_time is null and
                plan_center_arr_time >= concat(date_add(to_date(early_scan_time),2),' 07:00:00') then 6 else type end as type --20231225: 类型未到交接时间
     -- ,type
     ,truncated_time              --截单时间
     ,is_interrupted              --是否截单
     ,plan_collect_arr_time       --始发集散规划到达时间
     ,plan_center_arr_time        --始发中心规划到达时间
     ,actual_center_shipment_no   --实际首中心到件任务号     (20220903新增)
     ,customer_code               --客户编号                 (20220903新增）
     ,total_days_t                --T+x时效                  (20220903新增)
     ,center_planned_departure    --中心规划发车时间-1小时   (20220903新增)
     ,is_intime                   --时效是否准点             (20220903新增)
     ,express_type_code           --产品类型编码             (20221103新增)
     ,express_type_name           --产品类型名称             (20221103新增)
     ,last_area_code              --目的区/县编码            (20221113新增)
     ,last_area_name              --目的区/县名称            (20221113新增)
     ,last_city_code              --目的城市编码             (20221113新增)
     ,last_city_name              --目的城市名称             (20221113新增)
     ,last_agent_code             --目的代理区编码           (20221113新增)
     ,last_agent_name             --目的代理区名称           (20221113新增)
     ,first_nodal_send_time       --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time    --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time      --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time   --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late               --是否疫情件               (20221213新增)
     ,fast_total_days             --网点-网点T+X时效         (20221213新增)
     ,first_center_plan_send_time --始发中心规划发车时间     (20230103新增)
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       -- 网点到件发件任务单
     ,actual_nodal_shipment_no         -- 集散到件发件任务单
     ,first_network_plan_send_time        --始发网点规划发车时间 20230627
     ,null as first_nodal_plan_send_time  --始发集散规划发车时间 20230627 (网点-中心-网点, 没走集散)
     ,order_mark_en                    -- 订单标签英文
     ,order_mark_zh                    -- 订单标签中文
     ,second_nodal_code                   --第二集散code
     ,second_nodal_name                   --第二集散名称
     ,second_nodal_send_time              --第二集散发件时间
     ,second_nodal_arrival_time           --第二集散到件时间
     ,actual_second_collect_arr_time      --实际第二集散到件时间
     ,actual_second_nodal_shipment_no     --实际第二集散到件发件任务单
     ,second_nodal_plan_arrival_time      --第二集散规划到件时间
     ,second_nodal_plan_send_time         --第二集散规划发件时间
     ,car_waybill_split_flag
     ,direct_flag
from (
     select
           *
          ,case when actual_center_arr_time <= center_planned_departure then 1 else 0 end as is_intime --时效是否准点（20220903新增）
          ,row_number() over(partition by waybill_no order by data_num asc) as rn
     from (
          select
                wide.*
               ,route.total_days_t as total_days_t --T+x时效
               ,from_unixtime(unix_timestamp(concat(date_add(wide.scan_date,route.center_span_days + route.in_edge3_stop_days + route.in_edge3_stop_span_days),' ',route.first_center_plan_send_time))-3600,'yyyy-MM-dd HH:mm:ss') as center_planned_departure --中心规划发车时间-1小时（20220903新增）
               ,concat(date_add(wide.scan_date,route.center_span_days + route.in_edge3_stop_days + route.in_edge3_stop_span_days),' ',route.first_center_plan_send_time)as first_center_plan_send_time  -- 始发中心规划发车时间
               ,concat(date_add(wide.scan_date,route.reserve_1 + route.in_line_day + if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.network_plan_send_time)             as first_network_plan_send_time -- 始发网点规划发车时间
               ,concat(date_add(wide.scan_date,route.nodal_span_days + route.in_edge2_stop_days + route.in_edge2_stop_span_days + if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_send_time)    as first_nodal_plan_send_time    -- 始发集散规划发车时间
               ,concat(date_add(wide.scan_date,route.nodal2_span_days + route.extra_in_stop_days + route.extra_in_stop_span_days + if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.second_nodal_plan_send_time)  as second_nodal_plan_send_time   -- 始发集散2规划发车时间
               ,case when route.is_off = 1 then route.network_lasted_warehousing_time end as truncated_time --截单时间
               ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as is_interrupted  --是否截单
               ,concat(date_add(wide.scan_date,route.nodal_span_days+if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_arrival_time) as plan_collect_arr_time --始发集散规划到达时间
               ,concat(date_add(wide.scan_date,route.nodal2_span_days+if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)) ,' ',route.second_nodal_plan_arrival_time) as second_nodal_plan_arrival_time    --第二集散规划到达时间
               ,case
                     when wide.car_waybill_split_flag = 1 then concat(date_add(wide.scan_date,route.center_span_days + if(route.branch_in_center_span_days <> 0,route.branch_in_center_span_days,route.in_edge3_stop_days)),' ',route.first_center_plan_latest_arrival_time) -- 如果在车件分离配置中，取最晚到车时间
                     when wide.scan_time < route.first_warehousing_time --揽收时间<最早的最晚入仓时间
                         then concat(date_add(wide.scan_date,if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)+route.center_span_days),' ',route.first_center_plan_arrival_time) --无截单补充揽收跨天
                     when wide.scan_time >=route.first_warehousing_time and wide.scan_time <= route.lead_warehousing_time --揽收时间<下一个最晚入仓时间
                         then concat(date_add(wide.scan_date,if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)+route.center_span_days),' ',route.first_center_plan_arrival_time) --无截单补充揽收跨天
                     when wide.scan_time > route.network_lasted_warehousing_time --揽收时间>最晚的最晚入仓时间
                         then (case when route.is_off = 0 then concat(date_add(wide.scan_date,route.center_span_days+1),' ',route.first_center_plan_arrival_time) --揽收时间 大于 最晚的最晚入仓时间，且无截单，正常取，直接+1天
                                    else concat(date_add(wide.scan_date,route.center_span_days + if(route.branch_in_center_span_days <> 0,route.branch_in_center_span_days,route.in_edge3_stop_days)),' ',route.first_center_plan_latest_arrival_time)   --有截单，用全链路判责最晚到车时间
                          end)
                     end as plan_center_arr_time --始发中心规划到达时间
               ,case when wide.scan_time > route.network_lasted_warehousing_time
                         then unix_timestamp(concat(date_add(wide.scan_date,1),' ',
                         if(route.last_taking_time >= '00:00:00' and route.last_taking_time <= '06:00:00',route.first_warehousing_time,
                         if(route.last_taking_time = route.network_lasted_warehousing_time,route.last_taking_time,null)))) - unix_timestamp(wide.early_scan_time)
                     when nvl(unix_timestamp(concat(wide.scan_date,' ', route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time) >0
                         then nvl(unix_timestamp(concat(wide.scan_date,' ',route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time)
                     else 99999999999999999
                     end as data_num
          from (
               select
                    waybill_no                --运单号
                   ,order_source              --订单来源名称
                   ,goods_type_code           --物品类型编码
                   ,goods_type_name           --物品类型名称
                   ,last_network_code         --运单表目的网点编码
                   ,last_network_name         --运单表目的网点名称
                   ,early_scan_time           --最早扫描时间
                   ,early_scan_site_code      --最早扫描时间
                   ,first_nodal_code          --始发集散编码
                   ,first_nodal_name          --始发集散名称
                   ,first_center_code         --始发中心编码
                   ,first_center_name         --始发中心名称
                   ,actual_collect_code       --实际交件集散点编码
                   ,actual_collect_name       --实际交件集散点名称
                   ,actual_center_code        --实际交件中心编码
                   ,actual_center_name        --实际交件中心名称
                   ,collect_proxy_code        --交件代理区编码
                   ,collect_proxy             --交件代理区名称
                   ,actual_send_time          --网点实际发件时间
                   ,plan_collect_code         --规划到达集散编码
                   ,plan_collect_name         --规划到达集散名称
                   ,actual_collect_arr_time   --实际交件集散点时间
                   ,actual_center_arr_time    --实际首中心到车时间
                   ,receive_city_id           --收件城市id
                   ,receive_city              --收件城市
                   ,region_code               --收件管理大区编码
                   ,region_name               --收件管理大区名称
                   ,receive_proxy_code        --收件代理区编码
                   ,receive_proxy             --收件代理区名称
                   ,receive_franchisee_code   --收件加盟商编码
                   ,receive_franchisee        --收件加盟商名称
                   ,receive_network_code      --收件网点编码
                   ,receive_network_name      --收件网点名称
                   ,is_diff_proxy             --是否跨代理区
                   ,scan_time                 --揽收时间
                   ,scan_date                 --揽收日期
                   ,type                      --
                   ,actual_center_shipment_no   --实际首中心到件任务号     (20220903新增)
                   ,customer_code               --客户编号                 (20220903新增）
                   ,express_type_code           --产品类型编码             (20221103新增)
                   ,express_type_name           --产品类型名称             (20221103新增)
                   ,last_area_code              --目的区/县编码            (20221113新增)
                   ,last_area_name              --目的区/县名称            (20221113新增)
                   ,last_city_code              --目的城市编码             (20221113新增)
                   ,last_city_name              --目的城市名称             (20221113新增)
                   ,last_agent_code             --目的代理区编码           (20221113新增)
                   ,last_agent_name             --目的代理区名称           (20221113新增)
                   ,first_nodal_send_time       --始发集散发件扫描时间     (20221113新增)
                   ,first_nodal_arrival_time    --始发集散到件扫描时间     (20221113新增)
                   ,first_center_send_time      --始发转运中心发件扫描时间 (20221113新增)
                   ,first_center_arrival_time   --始发转运中心到件扫描时间 (20221113新增)
                   ,if_yiqin_late               --是否疫情件               (20221213新增)
                   ,fast_total_days             --网点-网点T+X时效         (20221213新增)
                   ,customer_name                    --客户名称
                   ,first_nodal_agent_code           --实际交件集散点代理区编码
                   ,first_nodal_agent_name           --实际交件集散点代理区名称
                   ,first_nodal_fran_code            --实际交件集散点加盟商编码
                   ,first_nodal_fran_name            --实际交件集散点加盟商名称
                   ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
                   ,taking_user_code                 --揽收业务员 20230516
                   ,taking_user_name                 --揽收业务员 20230516
                   ,actual_network_shipment_no       -- 网点到件发件任务单
                   ,actual_nodal_shipment_no         -- 集散到件发件任务单
                   ,order_mark_en                    -- 订单标签英文
                   ,order_mark_zh                    -- 订单标签中文
                   ,second_nodal_code
                   ,second_nodal_name
                   ,second_nodal_send_time
                   ,second_nodal_arrival_time
                   ,actual_second_collect_arr_time
                   ,actual_second_nodal_shipment_no
                   ,car_waybill_split_flag
                   ,direct_flag
               from jms_tmp.dwd_three_route_for_submit
               where plan_center_arr_time is null --网点-中心-网点路由
          ) wide
          left join jms_tmp.dwd_route_for_submit_tmp route
             on wide.early_scan_site_code = route.in_from_code  --按照始发网点、始发中心、目的网点关联兜底路由数据, 20240104:也会按照网点-网点关联兜底路由
             and
                 case when wide.first_center_code is null then 1=1
                      else wide.first_center_code = route.in_to_code
                 end
            -- and nvl(wide.first_center_code,0) = nvl(route.in_to_code,0)
            and wide.last_network_code = route.out_to_code
            and wide.scan_date = route.update_date
     ) t where data_num >0
) a where a.rn = 1;




-----------------------------------------------最后一步计算未到交接时间的数据
insert overwrite table jms_tmp.dwd_lasted_route_for_submit
select
      waybill_no                --运单号
     ,order_source              --订单来源名称
     ,goods_type_code           --物品类型编码
     ,goods_type_name           --物品类型名称
     ,last_network_code         --运单表目的网点编码
     ,last_network_name         --运单表目的网点名称
     ,early_scan_time           --最早扫描时间
     ,early_scan_site_code      --最早扫描时间
     ,first_nodal_code          --始发集散编码
     ,first_nodal_name          --始发集散名称
     ,first_center_code         --始发中心编码
     ,first_center_name         --始发中心名称
     ,actual_collect_code       --实际交件集散点编码
     ,actual_collect_name       --实际交件集散点名称
     ,actual_center_code        --实际交件中心编码
     ,actual_center_name        --实际交件中心名称
     ,collect_proxy_code        --交件代理区编码
     ,collect_proxy             --交件代理区名称
     ,actual_send_time          --网点实际发件时间
     ,plan_collect_code         --规划到达集散编码
     ,plan_collect_name         --规划到达集散名称
     ,actual_collect_arr_time   --实际交件集散点时间
     ,actual_center_arr_time    --实际首中心到车时间
     ,receive_city_id           --收件城市id
     ,receive_city              --收件城市
     ,region_code               --收件管理大区编码
     ,region_name               --收件管理大区名称
     ,receive_proxy_code        --收件代理区编码
     ,receive_proxy             --收件代理区名称
     ,receive_franchisee_code   --收件加盟商编码
     ,receive_franchisee        --收件加盟商名称
     ,receive_network_code      --收件网点编码
     ,receive_network_name      --收件网点名称
     ,is_diff_proxy             --是否跨代理区
     ,scan_time                 --揽收时间
     ,scan_date                 --揽收日期
     -- ,case when plan_center_arr_time >= concat(date_add(to_date(early_scan_time),2),' 07:00:00') then 6 else 0 end as type --类型 未到交接时间
     ,type     -- 20231025: 未到交接时间 并入应交件
     ,truncated_time            --截单时间
     ,is_interrupted            --是否截单
     ,plan_collect_arr_time     --始发集散规划到达时间
     ,plan_center_arr_time      --始发中心规划到达时间
     ,actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
     ,customer_code             --客户编号                 (20220903新增)
     ,total_days_t              --T+x时效                  (20220903新增)
     ,center_planned_departure  --中心规划发车时间-1小时   (20220903新增)
     ,is_intime                 --时效是否准点             (20220903新增)
     ,express_type_code         --产品类型编码             (20221103新增)
     ,express_type_name         --产品类型名称             (20221103新增)
     ,last_area_code            --目的区/县编码            (20221113新增)
     ,last_area_name            --目的区/县名称            (20221113新增)
     ,last_city_code            --目的城市编码             (20221113新增)
     ,last_city_name            --目的城市名称             (20221113新增)
     ,last_agent_code           --目的代理区编码           (20221113新增)
     ,last_agent_name           --目的代理区名称           (20221113新增)
     ,first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late             --是否疫情件               (20221213新增)
     ,fast_total_days           --网点-网点T+X时效         (20221213新增)
     ,first_center_plan_send_time --始发中心规划发车时间     (20230103新增)
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       --网点到件发件任务单
     ,actual_nodal_shipment_no         --集散到件发件任务单
     ,first_network_plan_send_time     --始发网点规划发车时间
     ,first_nodal_plan_send_time       --始发集散规划发车时间
     ,order_mark_en                    -- 订单标签英文
     ,order_mark_zh                    -- 订单标签中文
     ,second_nodal_code                --第二集散code
     ,second_nodal_name                --第二集散名称
     ,second_nodal_send_time           --第二集散发件时间
     ,second_nodal_arrival_time        --第二集散到件时间
     ,actual_second_collect_arr_time   --实际第二集散到件时间
     ,actual_second_nodal_shipment_no  --实际第二集散到件发件任务单
     ,second_nodal_plan_arrival_time   --第二集散规划到件时间
     ,second_nodal_plan_send_time      --第二集散规划发件时间
     ,car_waybill_split_flag
     ,direct_flag
from (
    -- select * from jms_tmp.dwd_collect_route_for_submit where type = 0 --网点-集散-应交件
    -- union all
    select * from jms_tmp.dwd_three_route_for_submit where plan_center_arr_time is not null -- and type = 0 --网点-集散-中心应交件
    union all
    select * from jms_tmp.dwd_four_route_for_submit -- where type = 0 --网点-中心应交件
);

-----------------------------最后结果判责
insert overwrite table jms_tmp.dwd_result_route_for_submit
select
      waybill_no
     ,order_source
     ,goods_type_code
     ,goods_type_name
     ,last_network_code
     ,last_network_name
     ,early_scan_time
     ,early_scan_site_code
     ,first_nodal_code
     ,first_nodal_name
     ,first_center_code
     ,first_center_name
     ,actual_collect_code
     ,actual_collect_name
     ,actual_center_code
     ,actual_center_name
     ,collect_proxy_code
     ,collect_proxy
     ,actual_send_time
     ,plan_collect_code
     ,plan_collect_name
     ,actual_collect_arr_time
     ,actual_center_arr_time
     ,receive_city_id
     ,receive_city
     ,region_code
     ,region_name
     ,receive_proxy_code
     ,receive_proxy
     ,receive_franchisee_code
     ,receive_franchisee
     ,receive_network_code
     ,receive_network_name
     ,is_diff_proxy
     ,type
     ,is_interrupted
     ,truncated_time
     ,plan_collect_arr_time
     ,plan_center_arr_time          as plan_center_arr_time  --规划首中心到车时间
     ,plan_center_arr_time          as hand_over_latest      --交接最晚到车时间
     ,to_date(early_scan_time)      as taking_date           --揽收日期
     ,to_date(plan_center_arr_time) as plan_arr_date         --规划到件日期
     ,is_arr_center_intime
     ,is_arr_collect_intime
     ,hand_over_intime as is_over
     ,case when hand_over_intime in (0,2) then 0 else 1 end as hand_over_intime
     ,case when hand_over_intime = 0 then early_scan_site_code
           when hand_over_intime = 2 then actual_collect_code end as hand_over_responsible_code --交接不准点责任所属机构编码
     ,case when hand_over_intime = 0 then receive_network_name
           when hand_over_intime = 2 then actual_collect_name end as hand_over_responsible      --交接不准点责任所属机构名称
     ,actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
     ,customer_code             --客户编号                 (20220903新增)
     ,total_days_t              --T+x时效                  (20220903新增)
     ,center_planned_departure  --中心规划发车时间-1小时   (20220903新增)
     ,is_intime                 --时效是否准点             (20220903新增)
     ,express_type_code         --产品类型编码             (20221103新增)
     ,express_type_name         --产品类型名称             (20221103新增)
     ,last_area_code            --目的区/县编码            (20221113新增)
     ,last_area_name            --目的区/县名称            (20221113新增)
     ,last_city_code            --目的城市编码             (20221113新增)
     ,last_city_name            --目的城市名称             (20221113新增)
     ,last_agent_code           --目的代理区编码           (20221113新增)
     ,last_agent_name           --目的代理区名称           (20221113新增)
     ,first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late             --是否疫情件               (20221213新增)
     ,fast_total_days           --网点-网点T+X时效         (20221213新增)
     ,case when substr(total_days_t,2,4) > substr(fast_total_days,2,4) then 1 else 0 end if_route_late  --路由晚点(20221213新增)
     ,case when hand_over_intime <> 1 and first_center_send_time <= first_center_plan_send_time then 1
           when hand_over_intime <> 1 and nvl(first_center_send_time,'2099-12-31') >  nvl(first_center_plan_send_time,'0000-00-00') then 0 end as if_center_send_timely --是否交件晚点及时发出1:是,0:否
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       --网点发件任务单
     ,actual_nodal_shipment_no         --集散到件发件任务单
     ,first_network_plan_send_time     --始发网点规划发车时间
     ,first_nodal_plan_send_time       --始发集散规划发车时间
     ,order_mark_en                    -- 订单标签英文
     ,order_mark_zh                    -- 订单标签中文
     ,second_nodal_code                --第二集散code
     ,second_nodal_name                --第二集散名称
     ,second_nodal_send_time           --第二集散发件时间
     ,second_nodal_arrival_time        --第二集散到件时间
     ,actual_second_collect_arr_time   --实际第二集散到件时间
     ,actual_second_nodal_shipment_no  --实际第二集散到件发件任务单
     ,second_nodal_plan_arrival_time   --第二集散规划到件时间
     ,second_nodal_plan_send_time      --第二集散规划发件时间
     ,car_waybill_split_flag
     ,direct_flag
from (
     select
           *
          ,case when actual_collect_arr_time <= plan_collect_arr_time then 1
                when actual_collect_arr_time >  plan_collect_arr_time then 0
                 end as is_arr_collect_intime --到达集散是否准点
          ,case when actual_collect_arr_time <= plan_center_arr_time then 1
                when actual_collect_arr_time >  plan_center_arr_time then 0
                 end as is_arr_center_intime  --到达中心是否准点
          ,case when nvl(plan_center_arr_time,'0000-00-00 00:00:00') < concat(date_add(to_date(early_scan_time),365),' 07:00:00') then  --始发中心规划发车时间<揽收日期+2天7点 (20231025: 不再有揽收日期+2天限制)
                    (case when early_scan_site_code is not null and first_nodal_code is not null and first_center_code is not null then --网点-集散-中心
                              (case when actual_center_arr_time <= plan_center_arr_time then 1
                                    when car_waybill_split_flag = 1 and first_center_send_time <= first_center_plan_send_time then 1    -- 在车件分离名单中,交件晚点及时发出也算准点
                                    when actual_collect_arr_time <= plan_collect_arr_time then 2
                                    else 0 end
                                  )
                          when early_scan_site_code is not null and first_nodal_code is null and first_center_code is not null then --网点-中心
                              (case when actual_center_arr_time <= plan_center_arr_time then 1 else 0 end)
                          when early_scan_site_code is not null and first_nodal_code is not null and first_center_code is null then --网点-集散
                              (case when actual_collect_arr_time <= plan_collect_arr_time then 2 else 0 end)
                          when early_scan_site_code is not null and first_nodal_code is null and first_center_code is null then 0 --网点-
                          else 0 end
                        ) end as hand_over_intime --交接是否准点  1准点 2集散责任 0网点责任
     from jms_tmp.dwd_lasted_route_for_submit
     where type = 0
) a;